How to flatten a hierarchy from a single join table

Question

I have a table that describes a hierarchical relationship with no bounds on the size. A simple example would be:

|ParentID |Child ID|
|1        |2       |
|2        |3       |
|1        |4       |
|5        |6       |

I need a query that for a given parent, gives a flat list of every child, all the way down, so for 3 it would just return 4, but for 1 it would return 2,3,4.

I'm coming fresh to MySQL from many years of SQL Server so am still getting used to how its more advanced query features work. There are a few examples of this on SO but they only work on hierarchies with fixed bounds on depth. I'm on MySQL 5.7 so sadly don't have CTEs.

You might like my answer to https://stackoverflow.com/a/192462/20860 or my presentation https://www.slideshare.net/billkarwin/recursive-query-throwdown — Bill Karwin, Sep 30 '19 at 14:37

score 0 · Answer 1 · answered Sep 30 '19 at 11:50

After a bit more Googling, I came up with something based on this question: How to create a MySQL hierarchical recursive query

select  distinct ChildID 
from    (select * from ParentChild) children,
        (select @ids := 1) top_level
where   find_in_set(ParentID, @ids)
and     length(@ids := concat(@ids, ',', ChildID))

I haven't tested it on some really large hierarchies but so far it seems to do what I want.

How to flatten a hierarchy from a single join table

1 Answers1