CODEWITHSUNDEEP
mysqlrelational-division
Samuel F
Samuel F

Reputation: 3

Using MySQL for relationnal division

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 ?

Upvotes: 0

Views: 193

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

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 |

demo http://sqlfiddle.com/#!9/8a331d/46

Upvotes: 1

Related Questions