T-SQL - How to write query to get records that match ALL records in a many to many join

Question

(I don't think I have titled this question correctly - but I don't know how to describe it)

Here is what I am trying to do:

Let's say I have a Person table that has a PersonID field. And let's say that a Person can belong to many Groups. So there is a Group table with a GroupID field and a GroupMembership table that is a many-to-many join between the two tables and the GroupMembership table has a PersonID field and a GroupID field. So far, it is a simple many to many join.

Given a list of GroupIDs I would like to be able to write a query that returns all of the people that are in ALL of those groups (not any one of those groups). And the query should be able to handle any number of GroupIDs. I would like to avoid dynamic SQL.

Is there some simple way of doing this that I am missing? Thanks, Corey

Answer 1

Basically you are looking for Persons for whom there is no group he is not a member of, so

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 2

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: thank you dotjoe!

Answer 3

You're basically not going to avoid "dynamic" SQL in the sense of dynamically generating the query at query time. There's no way to hand a list around in SQL (well, there is, table variables, but getting them into the system from C# is either impossible (2005 & below) or else annoying (2008)).

One way that you could do it with multiple queries is to insert your list into a work table (probably a process-keyed table) and join against that table. The only other option would be to use a dynamic query such as the ones specified by Jonathan and hongliang.