(SQL) Match users belong to which group given user_id[]

Question

user table

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

group 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

Given group of user ids : [1, 2, 3]

How to query the group that all users in the above list belongs to? (in this case: Group B)

Answer 1

To get all groups that contain exactly the specified users (i.e. all specified users and no 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 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