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

user table

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

group table

id | name 1  | group 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 query group users in above list belongs to? (in case: group b)

to groups contain specified users (i.e. specified users , no other users)

declare @numusers int = 3  select ug.group_id        --the max doesn't here because        --groups same group id have same name.         --max used can select group name eventhough        --we aren't aggregating across group names      , max(g.name) name user_group ug --filter groups 3 users join (select group_id user_group group group_id having count(*) = @numusers) ug2   on ug.group_id = ug2.group_id join [group] g     on ug.group_id = g.id user_id in (1, 2, 3) group ug.group_id --the distinct necessary if user_group --isn't keyed group_id, user_id having count(distinct user_id) = @numusers 

to groups contain specified users:

    declare @numusers int = 3          select ug.group_id            --the max doesn't here because            --groups same group id have same name.             --max used can select group name eventhough            --we aren't aggregating across group names          , max(g.name) name     user_group ug     join [group] g         on ug.group_id = g.id     user_id in (1, 2, 3)     group ug.group_id     --the distinct necessary if user_group     --isn't keyed group_id, user_id     having count(distinct user_id) = 3 

sql fiddle: http://sqlfiddle.com/#!6/0e968/3