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.
child.id is unique for every
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