Retrieving specific GroupId based on UserId's

Question

Here is a scenario where we wanna retrieve the specific GroupId no. Eg., 6 having only 3 users say UserId's with 2, 1, 3 with that specific Group

This is the query I tried to execute

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3) AND userid IN (1, 2, 3);

SQL Fiddle DEMO

Below are the details from the above query which had been broken into pieces

SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3

group_id
5
6

---

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3)

group_id
5
5
5
6
6
6

---

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3) AND userid IN (1, 2, 3)

group_id
5
5
6
6
6

---

SELECT group_id FROM group_users WHERE group_id IN(SELECT group_id FROM group_users GROUP BY group_id HAVING COUNT(*)=3) AND userid=1 AND userid=2 and userid = 3

group_id
NULL

---

Expected Result:

group_id
6

Since there is only one group with group_id = 6 having 3 users with unique userid = 1, 2, 3

So that group_id = 6 must be retrieved at the end result!

Answer 1

Use conditional counting in the HAVING clause. In MySQL, you can simply sum up met conditions, because true = 1 and false = 0 in MySQL.

SELECT group_id
FROM group_users 
GROUP BY group_id 
HAVING SUM(userid IN (1, 2, 3)) = 3
   AND SUM(userid NOT IN (1, 2, 3)) = 0;

Another, even simpler option is to concat the user IDs and check the string:

HAVING GROUP_CONCAT(userid ORDER BY userid) = '1,2,3';