SQL Server - find the person(s) that are each in all of the specified groups -

declare @person_groups table (person_id int not null, group_id int not null) insert @person_groups(person_id, group_id) values (1000, 501) --sample data insert @person_groups(person_id, group_id) values (1001, 501) --sample data insert @person_groups(person_id, group_id) values (1001, 502) --sample data insert @person_groups(person_id, group_id) values (1001, 503) --sample data insert @person_groups(person_id, group_id) values (1002, 502) --sample data insert @person_groups(person_id, group_id) values (1002, 503) --sample data  declare @tempgrouplist table (group_id int) insert @tempgrouplist(group_id) values (501) --sample data insert @tempgrouplist(group_id) values (502) --sample data insert @tempgrouplist(group_id) values (503) --sample data 

i need find person ids in table @person_groups each in every group listed in @tempgrouplist

pseudo code group list of 501, 502, 503,... :

select person_id    @person_groups   person_id member of group #501  , same person member of group #502    , same person member of group #503)     , on each group id contained in @tempgrouplist) 

i doing cursor (yucky, know). trying refactor on ms sql server (up 2012) not use cursor (nor dynamic sql) having trouble..

can shed light on solution?

select     g.person_id     @person_groups g         inner join     @tempgrouplist l         on g.group_id = l.group_id group     g.person_id having     count(distinct g.group_id) = (select count(*) @tempgrouplist); 

to explain slightly. inner join restricts groups in question. group by allows calculate number of different (distinct) groups each person in. having filters down people in right number of distinct groups.

if person can appear once in each group (i.e. group_id, person_id unique combination in @person_groups) don't need distinct.