Connect By Prior Equivalent for MySQL

Question

All,

I have three fields in a table that define a parent child relationship present in a MySQL database version 5.0 . The table name is tb_Tree and it has the following data:

Table Name: tb_Tree

Id | ParentId | Name
--------------------
1  | 0        | Fruits
2  | 0        | Vegetables
3  | 1        | Apple
4  | 1        | Orange
5  | 2        | Cabbage
6  | 2        | Eggplant

How do I write a Query to get all the children if a ParentId is specified. Note that the table entries given are just sample data and they can have many more rows. Oracle has a "CONNECT BY PRIOR" clause, but I didn't find anything similar for MySQL. Can anyone please advise?

Thanks

Answer 1

MySQL doesn't support recursive queries so you have to do it the hard way:

1. Select the rows where ParentID = X where X is your root.
2. Collect the Id values from (1).
3. Repeat (1) for each Id from (2).
4. Keep recursing by hand until you find all the leaf nodes.

If you know a maximum depth then you can join your table to itself (using LEFT OUTER JOINs) out to the maximum possible depth and then clean up the NULLs.

You could also change your tree representation to nested sets.