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.

Solution 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>
)

Solution 2

( I guess where you said "Child Eve references Eve" you meant "Child Eve references Bob", right?)

I think I've got it... looks ugly... the secret is the double negation... that is, everyone for which it's true,, is the same as not anyone for which is false... (ok, I have troubles with my english, but I guess you understand what I mean)

select * from parent

parent_id                               name
--------------------------------------- --------------------------------------------------
1                                       alice
2                                       bob

select * from child

child_id                                name
--------------------------------------- --------------------------------------------------
1                                       charlie
2                                       david
3                                       eve

select * from parent_child

parent_id                               child_id
--------------------------------------- ---------------------------------------
1                                       1
2                                       1
1                                       2
2                                       3

select * from parent p 
where not exists(
    select * from child c 
    where
        c.child_id in ( 1, 2, 3 ) and 
        not exists(
            select * from parent_child pc where
                pc.child_id = c.child_id and
                pc.parent_id = p.parent_id
        )
)

--when child list = ( 1 )
parent_id                               name
--------------------------------------- --------------------------------------------------
1                                       alice
2                                       bob

--when child list = ( 1, 2 )
parent_id                               name
--------------------------------------- --------------------------------------------------
1                                       alice

--when child list = ( 1, 2, 3 )
parent_id                               name
--------------------------------------- --------------------------------------------------

well, I hope it helps...

Solution 3

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
)

Comments

[gigimon]

That's the approach that I knew about; I was hoping to avoid the numerical trick. But your answer is worth an upvote anyway. :-)

[gigimon]

Yes, I munged the Eve->Bob reference. Thanks for pointing that out.