Hierarchical structure in MySql Please HELP!!!!

[mpandzo]

Hi,
I am working on a cms in ASP.NET with a mysql (4.1) database. I have a table called pages with fields pageID, parentPageID. I cant figure out a way to pull out hierarchicaly sorted data from this table...
For example
pageid=1, parentPageID=1
pageID=2, parentPageID=1
pageID=3, parentPageID=1
pageID=4, parentPageID=2
pageID=5, parentPageID=1
pageID=6, parentPageID=4
and so on ... its an n-ary tree...
I want to pull data out in the following order:
pageid=1
pageid=2
pageid=4 (because it is a child of pageid=2)
pageid=6 (because it is a child of pageid=4)
pageid=3
pageid=5

Also I cannot do it in the actual c# code because filtering and recursive traversal take too much away from the performance... If i were using mssql or oracle this would be really easy, but it seems mysql really sucks
Please help...

mpandzo

 

[r937]

ORDER BY coalesce(parentpageid,pageid)

however, this is only good for 1 level down

what you are asking for is some sort of tree traversal algorithm, and unless you can place a limit on the number of levels to traverse (which equates to the number of outer self-joins to write in the query), you'll have to do this recursively

and as far as i know (willing to be corrected on this!), microsoft sql server cannot do it recursively with sql, only oracle and db2 can

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)