Select Parent Record With All Children in SQL

Question

Let's say I have two tables, "Parent" and "Child". Parent-to-Child is a many:many relationship, implemented through a standard cross-referencing table.

I want to find all records of Parent that are referenced by ALL members of a given set of Child using SQL (in particular MS SQL Server's T-SQL; 2005 syntax is acceptable).

For example let's say I have:

• List item
• Parent Alice
• Parent Bob
• Child Charlie references Alice, Bob
• Child David references Alice
• Child Eve references Bob

My goals are:

• If I have Children Charlie, I want the result set to include Alice and Bob
• If I have Children Charlie and David, I want the result set to include Alice and NOT Bob.
• If I have Children Charlie, David, and Eve, I want the result set to include nobody.

Answer 1

Relying on a numerical trick (where the number of parent-child links = the number of children, that parent is linked to all children):

SELECT Parent.ParentID, COUNT(*)
FROM Parent
INNER JOIN ChildParent
    ON ChildParent.ParentID = Parent.ParentID
INNER JOIN Child
    ON ChildParent.ChildID = Child.ChildID
WHERE <ChildFilterCriteria>
GROUP BY Parent.ParentID
HAVING COUNT(*) = (
    SELECT COUNT(Child.ChildID)
    FROM Child WHERE <ChildFilterCriteria>
)

Answer 2

Here's an answer.

SQL query: Simulating an "AND" over several rows instead of sub-querying

And here's a specific application of that to this problem.

SELECT * FROM Parents
WHERE ParentId in
(
  SELECT ParentId FROM ChildParent
  WHERE ChildId in
  (
    SELECT ChildId FROM Child
    WHERE ChildName in ('Charlie', 'David')
  )
  GROUP BY ParentId
  HAVING COUNT(*) = 2
)