How do I recursively find all matching rows in a table?

Question

I'm trying to wrangle an existing database without resorting to tricks. I need to query within a linking table to find all matching id's recursively.

I've tried a number of nested join queries, but I'm not a SQL expert. I'm a programmer and while I work with simple databases complex queries like this are a struggle for me.

My table looks like this:

------------------------
| child_id | parent_ id|
________________________
    2           16
    3           16
    4           16 
    11          10
    12          11
    16          7
    17          10
    18          17
    19          7
    20          19
    21          10
    22          21
    23          22
    24          22
    26          20

I only know the top-level parent id. I want to find all associated child ID's.

If the parent ID was 7, I need to return 16,19,2,3,4,20,26.

I'm afraid I know the answer but you, by any chance, now the maximum depth, number of levels ? — Conffusion, Jul 26 '19 at 07:42
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — oniramarf, Jul 26 '19 at 07:49
The number of levels probably goes down to about 4 from a practical perspective, but is not theoretically limited. — Dan Gray, Jul 26 '19 at 07:51
https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/ — melpomene, Jul 26 '19 at 07:54
In case your mariadb version is lower than 10.2, then Common Table Expression is not available, and you may use "CONNECT BY" statement instead. — Mehdi, Jul 26 '19 at 07:58

score 1 · Accepted Answer · edited Jul 26 '19 at 08:08

1

NOTE: This solution will work in MariaDB 10.2.2 and above.

Try Common Table Expression:

with recursive cte(child_id, parent_id) as (
    select child_id, parent_id from MyTable
    where parent_id = 7    
       union all 
    select mt.child_id, mt.parent_id from MyTable mt
         inner join cte t on mt.parent_id = t.child_id
)
select * from cte;

edited Jul 26 '19 at 08:08

melpomene

84,125
8
85
148

answered Jul 26 '19 at 07:53

Pavel Smirnov

4,611
3
18
28

Thank you. I saw that possible duplicate. The query above makes some sense to me, but I get a syntax error when I try to execute it. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive cte(child_id, parent_id) as ( select child_id, parent_id from man' at line 1 – Dan Gray Jul 26 '19 at 08:01
@DanGray, which one? – Pavel Smirnov Jul 26 '19 at 08:02
@DanGray Which version of MariaDB are you using? Here's a [live demo](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=4ce65d148deeb2c90dc8805cf7f5656f). – melpomene Jul 26 '19 at 08:02
Yes - I'm on 5.7 which does't support recursive queries. – Dan Gray Jul 26 '19 at 08:04
Upgraded to mariadb 10. Works great now. Thanks you!! – Dan Gray Jul 26 '19 at 08:21
@DanGray - MySQL does not have CTEs until 8.0. – Rick James Jul 27 '19 at 00:35

How do I recursively find all matching rows in a table?

1 Answers1