I have 2 tables. For simplicity, first table Parent contains only id and name. Other table Child contains id and parent_id.

There are going to be many parent rows with the same name. They will have different childrens, so I want to be able to select a parent row only if it has childs from the list I provide.

Let's say I want a Parent with name 'test' only if it has Childs with id (223, 224, 225).

So far I achieved this with following query:

SELECT p.*, GROUP_CONCAT(c.id) as childs
FROM `parent` p
    INNER JOIN `child` c ON p.id = c.parent_id
WHERE p.name = 'test'
GROUP BY p.name
HAVING childs = '223,224,225'

However I think it's not a good solution if it uses id concatenation

  • 1
    What is exactly the question? Could you please add a sample data and a desired result? – McNets Apr 13 '17 at 14:42
  • You need ORDER BY c.id inside the GROUP_CONCAT. – Rick James Apr 13 '17 at 21:48

You can apply conditional aggregation

HAVING sum(case when c.id in (223,224,225) then 1 else -1 end) = 3

This assumes that those ids are unique.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.