Retrieving specific GroupId based on UserId's

user_7713811 0
Viewed 48

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!

mysql sql
2 Answers

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';
user_22707620

So, you are right. You have to intersect groups with exactly 3 users with groups that contain the minimum of 3 users (1,2,3).

Maria DB 10.2 onwards,

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

Click Here

For MySQL 5.6,

SELECT DISTINCT a.group_id FROM
(SELECT group_id FROM group_users 
GROUP BY group_id 
HAVING COUNT(*)=3) a,
(SELECT group_id FROM group_users 
WHERE userid IN (1, 2, 3)
GROUP BY group_id 
HAVING COUNT(*)=3) b
WHERE a.group_id=b.group_id

Click Here

user_108402700
Related