So lets say I have a table

ID  Car
1   Polo
2   Jetta

And a one-many relationship like

Id  Driver  Car
1   Jason   1
2   Fred    1
2   Fred    2
3   Batman  1
4   Frodo   2

How can I get records from car that Only has Fred and Frodo driving it? I.e. the query needs to return only car 2.

CodePudding user response：

Assuming that only has Fred and Frodo driving it means Fred, Frodo and no other, you would write:

SELECT car
FROM car_driver
GROUP BY car
HAVING COUNT(CASE WHEN driver IN ('Fred', 'Frodo') THEN 1 END) = 2
AND COUNT(*) = 2

CodePudding user response：

You can use the set operator INTERSECT:

SELECT car FROM drivers WHERE driver = 'Fred'
INTERSECT
SELECT car FROM drivers WHERE driver = 'Frodo'

db<>fiddle

CodePudding user response：

maybe something like this

select c.car 
from cardriver cd join cars c
   on c.id=cd.car and cd.Driver in ('Fred','Frodo')
group by c.car
   having count(distinct cd.Driver)=2

db fiddle here