MySQL 如何找到具有确切子集的父项？

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

但这也将获得包含这组 child 的父记录:1,2,3,5,7,9。我只想要那些拥有确切集合 child 的 parent :1,3,5,7,9。

有办法吗？

编辑:child.parent_id 和 child.fk 都不是唯一的。 child.fk 是链接到另一个表的外键。 (“多对多关系”)所以 parent 很可能有 child 1,2,3,5,7,9。我执行此查询的全部原因是尽量避免为 1,3,5,7,9 创建新的父级(如果这样的父级已经存在)。

Answer 1

假设 child.id 对于每个 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)