Hi For many days I have been working on this problem in MySQL, however I can not figure it out. Do any of you have suggestions?

Basically, I have a category table with domains like: id, name (name of category), and parent (id of parent of the category).

Example Data:

1  Fruit        0
2  Apple        1
3  pear         1
4  FujiApple    2
5  AusApple     2
6  SydneyAPPLE  5
....

There are many levels, possibly more than 3 levels. I want to create an sql query that groups the datas according to he hierarchy: parent > child > grandchild > etc.

It should output the tree structure, as follows:

1 Fruit 0
 ^ 2 Apple 1
   ^ 4 FujiApple 2
   - 5 AusApple 2
     ^ 6 SydneyApple 5
 - 3 pear 1

Can I do this using a single SQL query? The alternative, which I tried and does work, is the following:

SELECT * FROM category WHERE parent=0

After this, I loop through the data again, and select the rows where parent=id. This seems like a bad solution. Because it is mySQL, CTEs cannot be used.