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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With