SQL select users that belong to two groups

Question

I have a list of persons in a table. I then have another table where I correlate each person to one or more groups. Some persons have only one entry in the groups table but some have multiple.

I am now trying to SELECT list of persons that are in two specific groups. Person must be in BOTH groups in order to qualify.

My table with the basic information on the persons is base and the table with the group correlation is groups_registration. In fact I also have a third table where the groups names and further information are stored but it is not required for this query.

The groups I am trying to gather in this example are 4 and 11.

What I tried initially was:

SELECT base.*, groups_registration.person_id, groups_registration.group_id
        FROM base
                INNER JOIN groups_registration
                        ON base.id = groups_registration.person_id
        WHERE (groups_registration.group_id = '4' AND groups_registration.group_id = '11')
        ORDER BY base.name

This did not get my any response, I assume because no single row contains both group_id = 4 and group_id 11.

I have been searching through stackoverflow with no joy. Do you guys have any ideas?

Add some sample table data and the expected result. Formatted text please, not images. — jarlh, Feb 15 '18 at 13:09
Possible duplicate of https://stackoverflow.com/questions/15977126/select-rows-that-match-all-items-in-a-list — Salman A, Feb 15 '18 at 13:24

score 1 · Answer 1 · answered Feb 15 '18 at 13:10

Obviously, no row has both values. Use group by:

SELECT gr.person_id, groups_registration.group_id
FROM groups_registration gr
WHERE gr.group_id IN (4, 11)
GROUP BY gr.person_id
HAVING COUNT(DISTINCT gr.group_id) = 2;

I'll let you figure out how to join in the additional information from base.

Notes:

Use table aliases to make it easier to write and read queries.
Presumably, the ids are numbers. Compare numbers to numbers. Only use single quotes for date and string constants.
IN is better than long chains of OR/=.

Thanks a lot. This was the solution I simply did not see. Works like a charm :) — Guðmundur Karl, Feb 15 '18 at 13:55

score 1 · Answer 2 · answered Feb 15 '18 at 13:19

You can use joins as shown below:

SELECT A.*, B.person_id, B.group_id
FROM base A
INNER JOIN 
(SELECT gr.person_id, groups_registration.group_id
 FROM groups_registration gr
 WHERE gr.group_id IN (4, 11)
 GROUP BY gr.person_id
 HAVING COUNT(DISTINCT gr.group_id) = 2) B
 ON A.id = B.person_id;

This will give you all the desired fields.

SQL select users that belong to two groups

2 Answers2