Select query including a link table

Transact-SQL
1

Hello this is my first topic :smile:

I am studying Cs and i got a problem with a select queery.

I have been asked to find m.model,v.vehid,v.km,v.year at the vehicles that have got AirCondition but Not Nitro

i made up with this queery :

select distinct m.model,v.vehid,v.km,v.year
from vehicle v
	inner join models m on m.modid=v.modid
	inner join veh_extras ve on ve.vehid=v.vehid
	inner join extras e on ve.extraid=e.extraid and e.descr='AC'
		inner	join veh_extras ve2 on v.vehid=ve2.vehid
			inner join extras e2 on ve2.extraid=e2.extraid and e2.descr<>'NITRO'

but its Wrong :frowning: cause it returns the car that has got AC AND Nitro

This is the ScemaDiagram , , the result :frowning:

any help ?

2

Have you studied EXISTS / NOT EXISTS yet? or just JOIN?

Easiest, I think, would be (in pseudo code - I'll leave you to write the actual code ... come back and ask if you get stuck :slight_smile: )

FROM vehicle JOIN models (as you already have it) and then
WHERE EXISTS (vehicles with Extras = 'AC')
AND NOT EXISTS (vehicles with Extras = 'NITRO')

That should also mean that you don't need the DISTINCT

(Note that DISTINCT causes SQL to pull all the rows, sort them, then throw away the duplicates, which (in the real world!!) is a performance killer ... so best to try to write your code so that you don't need DISTINCT at all.

For example, EXISTS returns true if just one matching row exists. So its much more efficient than a JOIN, which will pull every possible matching row, if you then just throw away all the DUPs.

3

i get stucked :frowning: i cant figure out how to conect exist and not exist i made up with this

select distinct m.model,v.vehid,v.km,v.year
from vehicle v
	inner join models m on m.modid=v.modid
	inner join veh_extras ve on ve.vehid=v.vehid
	Where exists(select descr from extras where extras.extraid=ve.extraid and descr='AC')

well thats correct! it returns all the cars with 'AC' any help for the not exist connection ??

4 
SELECT m.model,v.vehid,v.km,v.year
FROM vehicle v
	INNER JOIN models m ON m.modid=v.modid
WHERE
    EXISTS(
        SELECT 1
        FROM veh_extras ve
        INNER JOIN extras e ON ve.extraid=e.extraid
        WHERE
            ve.vehid=v.vehid AND 
            e.descr='AC'
        )
    AND NOT EXISTS(
        SELECT 1
        FROM veh_extras ve
        INNER JOIN extras e ON ve.extraid=e.extraid
        WHERE
            ve.vehid=v.vehid AND 
            e.descr='NITRO'
        )
2 Likes
5

thank you sir

6

I am trying to figure out how the connections work and how the CONDITION AND Works in order to return

the result i want can you give me an example ??

do i compare all the rows of the first select with the rows of the second ? (NOT EXIST ) and i return the same ?

Example

7

you can also do

:

 `select  m.model,v.vehid,v.km,v.year
from vehicle v,Extras e,veh_extras ve,Models m
	where m.modid=v.modid and ve.vehid=v.vehid and e.extraid=ve.extraid and e.descr='AC'
	

	except select  m.model,v.vehid,v.km,v.year
from vehicle v,Extras e,veh_extras ve,Models m
	where m.modid=v.modid and ve.vehid=v.vehid and e.extraid=ve.extraid and e.descr='NITRO'
	`