I need to execute a query with a relationnal division
What I have : 2 tables with a 3rd table for Many-to-Many relation
=> Meetings have multiple participants and participants can attend multiple meetings
What I need : get the meetings which have at least the given participants
Here a SQL query which do the job :
SELECT
m.meeting_id,
m.name,
m.status
FROM meetings As m
INNER JOIN meetings_participants AS mp ON m.meeting_id = mp.meeting_id
WHERE m.status <> 'temporary'
AND mp.participant_id IN (1, 2)
GROUP BY m.meeting_id
HAVING COUNT(DISTINCT mp.participant_id) >= 2
SQL Fiddle : http://sqlfiddle.com/#!9/8a331d/6/0
Question 1 : is there a way to select what I need without the 'HAVING COUNT...' part ?
Question 2 : and in a second query, I need the meetings with exactly the given participants, how to do it ?
Question 1 : is there a way to select what I need without the 'HAVING COUNT...' part ?
Yes you can use multiple JOIN's but that isn't a very good solution.because you need a JOIN for every mp.participant_id... The Query you have is much more scalable
Query
SELECT
m.meeting_id
, m.name
, m.status
FROM
meetings AS m
INNER JOIN meetings_participants AS mp1 ON m.meeting_id = mp1.meeting_id AND m.status <> 'temporary' AND mp1.participant_id = 1
INNER JOIN meetings_participants AS mp2 ON m.meeting_id = mp2.meeting_id AND m.status <> 'temporary' AND mp2.participant_id = 2
Result
| meeting_id | name | status |
|------------|-----------|-----------|
| 1 | a meeting | active |
| 5 | e meeting | cancelled |
demo http://sqlfiddle.com/#!9/8a331d/54
Question 2 : and in a second query, I need the meetings with exactly the given participants
You need to find the COUNT first for every meeting_participants HAVING two records
With
SELECT
meeting_id
FROM
meetings_participants
GROUP BY
meeting_id
HAVING COUNT(*) = 2
And use that within the main query like so.
Query
SELECT
meetings.*
FROM (
SELECT
meeting_id
FROM
meetings_participants
WHERE
participant_id IN(1, 2)
AND
meeting_id IN(
SELECT
meeting_id
FROM
meetings_participants
GROUP BY
meeting_id
HAVING COUNT(*) = 2
)
GROUP BY
meeting_id
HAVING COUNT(*) = 2
)
meetings_participants
INNER JOIN
meetings
ON
meetings_participants.meeting_id = meetings.meeting_id
Result
| meeting_id | name | status |
|------------|-----------|-----------|
| 5 | e meeting | cancelled |