MySql - Get all children in JOIN if one child matches a condition

Question

I have a one-to-many relationship between two tables Parent and Child. I want to get all children if one child matches a condition. Something like below:

SELECT * FROM Parent p
INNER JOIN Child c ON c.parent_id = p.id
WHERE c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'

This query returns only the child with name containing abc. I want to get all siblings of this child as well. How can i do that?

Answer 1

SELECT p.*, c2.*
FROM Parent p
INNER JOIN Child c 
    ON c.parent_id = p.id
INNER JOIN Child c2
    ON c.parent_id = c2.parent_id
WHERE
    c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'