2

I have a Person table, Group table, and Group_Person table, where many person entities can have many group entities. Group_Person acts as the intermediate table, and have the tuple (group_id, person_id) that maps to group_id from Group table, and person_id from Person table.

Given 0 or more person_id from Person table (that is queried from Person table), I want to find all Groups from Group table where, in Group_Person table, there is a tuple (group_id, person_id) for each person_id. In other words, each person_ids is mapped to a common group_id.

Since MySQL has no INTERSECT function, I was thinking of doing the following query (this returns only the group_id):

SELECT t1.group_id 
FROM Group_Person AS t1 
JOIN Group_Person AS t2 ON t1.group_id = t2.group_id 
WHERE t1.person_id = xxx AND t2.person_id = yyy;

Again, there can be 0 or more person_ids here. Is this the best solution?

krtek
  • 26,334
  • 5
  • 56
  • 84
ZachC
  • 91
  • 1

1 Answers1

3

I don't think joining group_person multiple times'll do it for you. You probably want something like this:

SELECT group_id
FROM Group_Person
WHERE person_id in(put the ids here)
GROUP BY group_id
HAVING count(group_id) = the count of person_ids
jswolf19
  • 2,303
  • 15
  • 16