Link to home
Start Free TrialLog in
Avatar of interclubs

asked on

Recursive MySQL Select (2 Levels)

I have a mysql table called 'categories' and it designed as such:
id | parentid | catname

and it contains data such as:
1 | null | Gaming
2 | 1 | Nintendo
3 | 1 | XBOX 360
4 | null | Environment
5 | 4 | Green Technology

I am trying to do a select that will give me:

1, Gaming
2, Gaming > Nintendo
3, Gaming > XBOX 360
4, Environment
5 , Environment > Green Technology

I can't for the life of me figure it out. Right now there are only 2 levels to the data, so the query doesn't need to work with unlimited nested categories (although it would be nice if it did). I've looked everywhere and posted in a ton of forums, but recursive selects seem to be a very hard thing to get answers on.....
Avatar of drazen_nikolic
Flag of Serbia image

I am not sure about recursive select in MySQL but I think there is no such functionality supported in that database. Oracle, for example, has this kind of recursive query - CONNECT BY (, for this way of storing hierarchical data, so called "The Adjacency List Model".

There is, also a different approach for storing hierarchical data: Modified Preorder Tree Traversal. Each tree node has its unique left and right "weight" value. The advantage of this approach is easy and performant and disadvantage is additional processing needed when you add/remove a node into the structure.

Check next article for details:
Avatar of drazen_nikolic
Flag of Serbia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial