CODEWITHSUNDEEP
sqlsql-server
user3348622
user3348622

Reputation: 1

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 into @person_groups(person_id, group_id) values (1000, 501) --sample data
insert into @person_groups(person_id, group_id) values (1001, 501) --sample data
insert into @person_groups(person_id, group_id) values (1001, 502) --sample data
insert into @person_groups(person_id, group_id) values (1001, 503) --sample data
insert into @person_groups(person_id, group_id) values (1002, 502) --sample data
insert into @person_groups(person_id, group_id) values (1002, 503) --sample data

declare @tempGroupList table (group_id int)
insert into @tempGroupList(group_id) values (501) --sample data
insert into @tempGroupList(group_id) values (502) --sample data
insert into @tempGroupList(group_id) values (503) --sample data

I need to find the person ids in table @person_groups who are each in every group listed in @tempGroupList

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

SELECT person_id 
FROM   @person_groups 
WHERE  person_id is a member of group #501 
and that same person is also a member of group #502
   and that same person is also a member of group #503) 
   and so on for each group id contained in @tempGroupList)

I am currently doing this with a cursor (yucky, I know). I am trying to refactor this on MS SQL Server (up to 2012) to not use a cursor (nor dynamic sql) but am having some trouble..

Can anyone shed some light on a good solution?

Upvotes: 0

Views: 47

Answers (1)

Laurence
Laurence

Reputation: 10976

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

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

If a person can only appear once in each group (i.e. group_id, person_id is a unique combination in @person_groups) then you don't need the distinct.

Upvotes: 2

Related Questions