MySQL: How to find all IDs of children recursively?

Question

I would like to get all IDs from children in a tree with MySQL only.

I have a table like this:

ID parent_id name
1  0         cat1
2  1         subcat1
3  2         sub-subcat1
4  2         sub-subcat2
5  0         cat2

Now I'm trying to get all child IDs for cat1 (2,3,4) recursively. Is there any way how to achieve that?

Thanks for all your help!

Answer 1

There are two basic methods for doing this: adjacency lists and nested lists. Take a look at Managing Hierarchical Data in MySQL.

What you have is an adjacency list. No there isn't a way of recursively grabbing all descendants with a single SQL statement. If possible, just grab them all and map them all in code.

Nested sets can do what you want but I tend to avoid it because the cost of inserting a record is high and it's error-prone.

Answer 2

You could probably do it with a stored procedure, if that's an option for you.

Otherwise you can't do it with a single sql-statement.

Ideally you should make the recursive calls to walk the tree from your program

Answer 3

Your question seems a bit imprecise. Why do you want to have them, and what do you mean by having them, "in a tree" ?

The table you've got IS (the relational way to represent) the tree.

If you want them "in a table" with rows that hold the pairs (ID 4 , ParentID 0), then you need your SQL engine's version of recursive SQL to do this, if that engine supports it.

I wouldn't know about MySQL specifically, but my understanding is that they once planned to implement recursive SQL using the same syntax as Oracle, i.e. with CONNECT BY.

If you look in your manual's table of contents for keywords such as "recursive queries" or "CONNECT BY", I imagine you should be able to find the answer.

(Sorry for not being able to provide a more ready-to-consume answer.)

Answer 4

If changing your table strcuture is an option, then what you need is the nested set model. This allows you to retrieve entire sections of a tree structure with a single non-nested SELECT. It's simple but clever.