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:
name (name of category), and
parent (id of parent of the category).
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.