SQL get all parent / children joined rows if any child passes conditional

Question

I would like to select a parent and all of its child nodes if any particular child matches a set of criteria. Is this possible with a single query?

Using two queries it would look like this:

SELECT p_id INTO target 
FROM parent, children 
WHERE parent.p_id = children.p_id and children.data1 = 'blah';

SELECT * 
FROM parent 
JOIN children ON parent.p_id = children.p_id and children.p_id = target;

.

p_id    name
1       Perice
2       Siward
3       Scottie

c_id    p_id    name
1          1    Arda
2          1    Katrinka
3          2    Gladi
4          3    Hedi
5          3    Didi
6          3    Lorry

if the predicate was a child name match for Lorry, then the result set would contain

p_id c_id p_name    c_name
3    4    Scottie   Hedi
3    5    Scottie   Didi
3    6    Scottie   Lorry

Does this answer your question? [Multiple SQL query in C#](https://stackoverflow.com/questions/44626839/multiple-sql-query-in-c-sharp) — Ali Bigdeli, Jul 29 '20 at 17:56
Maybe `select p.p_name, c* from parents as p join children as c using(p_id) where c.data1 = 'blah'` is what you want? — user3783243, Jul 29 '20 at 18:36
Are you using MySQL 8.0? If so, the solution would involve a [recursive CTE](https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples). If you are using MySQL 5.x, then you would need to store your data differently and use one of the workarounds for hierarchical data. See my presentation [Models for Hierarchical Data](https://www.slideshare.net/billkarwin/models-for-hierarchical-data). — Bill Karwin, Jul 29 '20 at 18:39

Luuk · Answer 1 · 2020-07-29T18:16:57.657

-1

SELECT parent.*, children.*
FROM parent
INNER JOIN children ON parent.p_id = children.p_id and children.data1 = 'blah';

EDIT after comment:

SELECT parent*
FROM parent
WHERE parent.p_id IN (selet children.p_id 
                       FROM childeren 
                       WHERE children.p_id=parent.p_id and chidren.data1='blah')

edited Jul 29 '20 at 18:16

answered Jul 29 '20 at 18:01

Luuk

12,245
5
22
33

this would only select the one child – cubesnyc Jul 29 '20 at 18:03
you should add some sample data + desired output... – Luuk Jul 29 '20 at 18:14

SQL get all parent / children joined rows if any child passes conditional

1 Answers1