Link to home
Start Free TrialLog in
Avatar of interclubs
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
etc...

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
etc....

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
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 (http://www.psoug.org/reference/connectby.html), 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: http://www.sitepoint.com/article/hierarchical-data-database/2
ASKER CERTIFIED SOLUTION
Avatar of drazen_nikolic
drazen_nikolic
Flag of Serbia image

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