0

I have two tables

parent{
    parent_id
}

child{
    child_id
    parent_id
}

I need to find a parent with parameters are children id

Ex: Find a parent has exactly children (1, 2, 3)

I tried "Where child_id IN (1, 2, 3)" but it will return incorrect parent in case parent has children(1, 2, 3, 4)

I can hard code with php but I ask for mysql solution.

complez
  • 7,882
  • 11
  • 48
  • 56

2 Answers2

1

Maybe add a COUNT() condition, if a parent has (1,2,3) for children and 3 children it's all good !

SELECT *
FROM parent NATURAL JOIN child
WHERE child.child_id IN (1, 2, 3)
GROUP BY parent.parent_id
HAVING COUNT(child.child_id) = 3

(not tested)

MatTheCat
  • 18,071
  • 6
  • 54
  • 69
  • hope this may be helpful : http://stackoverflow.com/questions/11064913/achieve-hierarchy-parent-child-relationship-in-an-effective-and-easy-way – Sashi Kant Jul 01 '13 at 08:19
0

I found a solution

SELECT parent_id, child_id, count(child_id) as n, sum(child_id) as m FROM child
group by parent_id
HAVING child_id IN (1, 2, 3) AND n = 3 AND m = 6
complez
  • 7,882
  • 11
  • 48
  • 56