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.
You can use the set operator INTERSECT:
SELECT car FROM drivers WHERE driver = 'Fred'
INTERSECT
SELECT car FROM drivers WHERE driver = 'Frodo'
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
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