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

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.

Answer

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;