MySQL how to find parent with exact set of children?

Question

MySQL 5.5

parent table:
id | facts

child table:
parent_id | foreign_key | facts

Now, I want to find parents that have a certain exact set of children, no more, no less. Something like:

SELECT t1.`id` 
from `parent_table` t1 
  LEFT JOIN `child_table` t2 ON t1.id=t2.parent_id
WHERE t2.`fk` = 1 
  AND t2.`fk` = 3  
  AND t2.`fk` = 5 
  AND t2.`fk` = 7 
  AND t2.`fk` = 9

But this will also get a parent record with this set of children: 1,2,3,5,7,9. And I only want those parents that have the exact set of children: 1,3,5,7,9.

Is there a way?

EDIT: child.parent_id and child.fk are both not unique. child.fk is a foreign key linking to another table. ("many-to-many relationship") So it is quite possible for a parent to have children 1,2,3,5,7,9. My whole reason for doing this query is to try to avoid creating a new parent for 1,3,5,7,9 if such a parent already exists.

Answer 1

Assuming that child.id is unique for every child.parent_id.

SELECT  a.id, a.facts
FROM    parent a
        INNER JOIN child b
            ON a.id = b.parent_ID
WHERE   b.id IN (1,3,5,7,9) AND        -- <<== list all ChildID here
        EXISTS                         -- <<== this part checks if the parent_ID
        (                              --           present on the EXISTS clause
            SELECT  parent_ID          --           which only filters parents
            FROM    child c            --           with 5 children
            WHERE   b.parent_ID = c.parent_ID
            GROUP   BY parent_ID
            HAVING  COUNT(*) = 5       -- <<== total number of children
        )
GROUP   BY a.id, a.facts
HAVING  COUNT(*) = 5                   -- <<== total number of children

• SQLFiddle Demo (more info included)

Answer 2

This problem is called (exact) relational division. There is a lot of useful code and explanation in this article: Divided We Stand: The SQL of Relational Division.

One way to solve it:

SELECT p.id AS parent_id
FROM parent AS p
WHERE EXISTS
      ( SELECT * FROM child AS c
        WHERE c.fk = 1 AND c.parent_id = p.id)
  AND EXISTS
      ( SELECT * FROM child AS c
        WHERE c.fk = 3 AND c.parent_id = p.id)
  AND EXISTS
      ( SELECT * FROM child AS c
        WHERE c.fk = 5 AND c.parent_id = p.id)
  AND EXISTS
      ( SELECT * FROM child AS c
        WHERE c.fk = 7 AND c.parent_id = p.id)
  AND EXISTS
      ( SELECT * FROM child AS c
        WHERE c.fk = 9 AND c.parent_id = p.id)
  AND NOT EXISTS
      ( SELECT * FROM child AS c
        WHERE c.fk NOT IN (1,3,5,7,9) AND c.parent_id = p.id) ;

And another link to a similar question, here at StackOverflow, where you'll find more than 10 different solutions (note: it's not for the exact division but for the division with remainder) and performance tests (for Postgres): How to filter SQL results in a has-many-through relation