T-SQL - How to write a query to retrieve records that match all records in many ways

Question

(I don’t think I correctly named this question, but I don’t know how to describe it)

Here is what I am trying to do:

Say I have a Person table that has a PersonID field. And let them say that Man can belong to many groups. Thus, there is a Group table with a GroupID field and a GroupMembership table, which is a many-to-many join between two tables, and the GroupMembership table has a PersonID field and a GroupID field. So far, it has been a lot for many.

Given the list of GroupIDs, I would like to write a query that returns all the people who are in ALL of these groups (none of these groups). And the request should be able to process any number of GroupIDs. I would like to avoid dynamic SQL.

Is there any easy way to do this that I am missing? Thanks, Corey

Answer 1

select person_id, count(*) from groupmembership where group_id in ([your list of group ids]) group by person_id having count(*) = [size of your list of group ids] 

Edited: thanks dotjoe!

Answer 2

Basically you are looking for people for whom there is no group of which he is not a member, therefore

 select * from Person p where not exists ( select 1 from Group g where not exists ( select 1 from GroupMembership gm where gm.PersonID = p.ID and gm.GroupID = g.ID ) ) 

Answer 3

In principle, you cannot avoid "dynamic" SQL in the sense of dynamically generating a query during a query. There is no way to pass the list into SQL (well, there are table variables, but getting them in a C # system is either impossible (2005 and below) or annoying (2008)).

One way to do this with multiple queries is to insert your list into a worksheet (possibly a table with a process key) and join this table. The only other option is to use a dynamic query, such as those indicated by Jonathan and Hunlian.