CODEWITHSUNDEEP
mysqlsql
johan
johan

Reputation: 77

Select parent records with all children in MySQL

I have 3 tables:

There is a many to many relationship between parent and child, realised with a parentchild join table.

I'm looking for a query which will give me all the parents for a given list of child ids. BUT - it has to match exactly, i.e. the parent has to match only if it has EXACTLY the given child id set, i.e. not less, but also not more (I'm struggle with the "not more" part).

I have a similar requirement as this: Select Parent Record With All Children in SQL except that the children can have multiple parents.

To give a concrete example:

select * from parent

parent_id  name
------------------------------------
1          Parent 1
2          Parent 2

select * from child

child_id  name
------------------------------------
1         Child 1
2         Child 2
3         Child 3

select * from parent_child

parent_child_id  parent_id  child_id
------------------------------------
1                1          1
2                1          2
3                1          3
4                2          1
5                2          3

I.e. if providing child ids 1,2,3 must return parent id 1, since only parent 1 is linked to all 3 given child ids.

Providing child ids 1 and 3, must return parent id 2, since only parent 2 is linked to only child 1 and 3

This is my 1st question on here - hope it's ok!

Upvotes: 1

Views: 2838

Answers (1)

user359040
user359040

Reputation:

Try:

select parent_id
from parent_child
group by parent_id
having count(child_id) = 2 and
       count(case when child_id in (1,3) then child_id end) = 2

- for child_id set (1,3). For child_id set (1,2,3), update the in condition accordingly, and change both = 2 conditions to be = 3.

Upvotes: 1

Related Questions