mysql - Find parent with child

Question

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.

Answer 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)

Answer 2

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