Query an AND in a many-to-many table

user_629110 0
Viewed 35

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.

sqlite sql
2 Answers

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.

user_104988280

A fun way to do it is to (mis)use LISTAGG:

WITH cteAttendees
  AS (SELECT pp.PRESENTATION_ID,
             ',' || LISTAGG(pp.PEOPLE_ID, ',') WITHIN GROUP (ORDER BY pp.PEOPLE_ID) || ',' AS ATTENDEES
  FROM PEOPLE_PRESENTATIONS pp
  GROUP BY pp.PRESENTATION_ID)
SELECT *
  FROM cteAttendees a
  WHERE a.ATTENDEES LIKE '%,1,%' AND
        a.ATTENDEES LIKE '%,2,%'

db<>fiddle here

user_2131360
Related