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

join;MySQL

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?

Best Answer

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%'

Related Solutions

Thesql inner join producing more results than it should

Why are you using two syntaxes, old fashioned verbs, roles and then an explicit inner join? Makes the query very hard to read and since you don't have any where clause that defines the relationship between verbs and roles (this is one of the biggest problems with using old fashioned syntax), you essentially have the cartesian product of those first two tables, and this probably explains your row counts.

You need to define the join columns between verbs and roles and, preferably, put that into an ON clause with a proper INNER JOIN. E.g.:

SELECT r.name, v.name, p.enabled
FROM verbs AS v
INNER JOIN privileges AS p
  ON v.id = p.verb_id
INNER JOIN roles AS r
  ON p.role_id = r.id;

Mysql – SQL to select all siblings with specific child

In the WHERE you can simply filter for the parts which have an assemblie id equal to the assemblie id of 'Part B'

SELECT a.*, p.* 
FROM assemblies a
LEFT JOIN parts p USING (AID)
WHERE p.AID IN 
  (
    SELECT AID FROM parts WHERE P_NAME='Part B'
  );

Best Answer

Related Solutions

Thesql inner join producing more results than it should

Mysql – SQL to select all siblings with specific child

Related Question