(SQL) Relevant users belong to which group user_id [] is specified

Question

user table

ID | name 1 | ada 2 | bob 3 | tom 

Table

Table

 ID | name 1 | group A 2 | group B 3 | group C 

user_group table

 user_id | group_id 1 | 1 2 | 1 1 | 2 2 | 2 3 | 2 1 | 3 3 | 3 

This group of user identifiers: [1, 2, 3]

How to request a group to which all users from the above list belong? (in this case: group B)

Answer 1

To get all groups that contain exactly specified users (i.e. all specified users and other users)

 DECLARE @numUsers int = 3 SELECT ug.group_id --The Max doesn't really do anything here because all --groups with the same group id have the same name. The --max is just used so we can select the group name eventhough --we aren't aggregating across group names , MAX(g.name) AS name FROM user_group ug --Filter to only groups with three users JOIN (SELECT group_id FROM user_group GROUP BY group_id HAVING COUNT(*) = @numUsers) ug2 ON ug.group_id = ug2.group_id JOIN [group] g ON ug.group_id = g.ID WHERE user_id IN (1, 2, 3) GROUP BY ug.group_id --The distinct is only necessary if user_group --isn't keyed by group_id, user_id HAVING COUNT(DISTINCT user_id) = @numUsers 

To get groups that contain all the specified users:

  DECLARE @numUsers int = 3 SELECT ug.group_id --The Max doesn't really do anything here because all --groups with the same group id have the same name. The --max is just used so we can select the group name eventhough --we aren't aggregating across group names , MAX(g.name) AS name FROM user_group ug JOIN [group] g ON ug.group_id = g.ID WHERE user_id IN (1, 2, 3) GROUP BY ug.group_id --The distinct is only necessary if user_group --isn't keyed by group_id, user_id HAVING COUNT(DISTINCT user_id) = 3 

SQL Fiddle: http://sqlfiddle.com/#!6/0e968/3

Answer 2

Try the following:

 Select t2.name FROM (Select group_id From user_group Group by group_id Having Count(user_id) = (Select Count(*) FROM User_Table)) AS T1 INNER JOIN Group_Table AS T2 ON T1.group_id = T2.ID 

See Fiddle: http://sqlfiddle.com/#!2/fa7250/4

Answer 3

 Select UserID,count(*) From UserGroupTable group by UserID 

This will give a score of 3 where the user id / group id is unique (as indicated by zerkms)

Answer 4

 SELECT name FROM group_tbl WHERE id IN (SELECT g_id FROM user_grp GROUP BY g_id HAVING Count(u_id)=(SELECT Count(id) FROM user_tbl));