Query an AND in a many-to-many table

I know that this is a relatively simple question, I just can’t figure it out. I have three tables: people, presentations, and people_presentations. people and presentations both have ID columns, and people_presentations that has foreign keys to each of people and presentations.

For example:

people:

ID | Name
1  | John
2  | Ian
3  | Mike

presentations:

ID | Title
1  | A new hope
2  | The Empire Strikes Back
3  | Return of the Jedi

people_presentations:

people_ID | presentation_ID
        1 | 1
        2 | 1
        2 | 2
        1 | 3
        3 | 3

I am trying to select all presentation IDs where a specific subset of people is attached. For ORs this is relatively easy: SELECT DISTINCT presentation_ID FROM people_presentations WHERE people_ID IN (1,2);, but for the AND case… I just can’t figure it out. This needs to work for N people, so I’d rather not have infinite JOINs.

Answer

You can filter the table for the people that you want, group by presentation and set the condition in the HAVING clause:

SELECT presentation_ID
FROM people_presentations
WHERE people_ID IN (1, 2)
GROUP BY presentation_ID
HAVING COUNT(*) = 2 -- the number of people

See the demo.