Using MySQL query to traverse rows to make a recursive tree
Solution 1:
Back in October 24, 2011, someone posted a question in the DBA StackExchange about tree traversal in MySQL. The SQL for MySQL cannot support it.
I wrote up three(3) Stored Procedures (GetParentIDByID, GetAncestry and GetFamilyTree) in my answer to that question. Hope this information helps you construct what you are looking for.
Solution 2:
Bill Karwin has posted a slide show about heirarchical data in MySQL. If changing your database design is an option, there are some other appealing ways to store your data to make it easier to query. The approaches he covers are:
- Adjacency List
- Path Enumeration
- Nested Sets
- Closure Table
Slide 69 has a nice table showing the pros and cons of each method, so I suggest you look at that slide first to see which approach might work for you, then go back and look at the details of how to implement it. Note that the design you have chosen (adjacency list) is the only one of the four designs presented that makes it hard to query a subtree.
Having said that, if you can't change your design or you want to stick with the adjacency list then I have to agree with Didier that you should take a look at Quassnoi's article "Hierarchical queries in MySQL". It is a very clear article and explains how to write the query efficiently.
Solution 3:
AFAIK, it is non trivial to do this with MySQL.
Here is a good set of articles about it:
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/
Solution 4:
MySQL (8) nowadays supports recursive queries.
Considering your table item(id, parent) and a starting item with id = 1, the following would do the job:
with recursive result(id, parent) as (select id, parent from item where id = 1 union all select i.id, i.parent from item i join result on i.parent = result.id) select * from result;