SQL - a menu system - plz help

[Summasummarum]

Hi,

As subj says. A simple menu is defined in a table. But how to extract it?
Heres the deal:

Table menu has these 3 columns: menuid parent menudesc

Ok this should be easy right? Those menu-rows that has parent=0 is
"toplevel". And so forth. An example:

1 0 "Items"
2 0 "Standings"
3 0 "Test"
4 2 "Liverpool"
5 2 "AC Milan"
6 1 "Itemno1"

This menu should in layout come in this order:

Items
Itemno1
Test
Standings
AC Milan
Liverpool

That is: Alphabetically sorted and with the children underneath the parents.
Right? Yes - and selecting the menu and doing some gymnastics in ASP isnt
that hard. But ASP must be the language that has the "crapiest" way of
handling arrays...so a simple task is really messy. Creating a stored
procedure involes 2-3 temporary tables as I see it. Not vey elegant either.

So my question is: Isnt there any way of selecting this order directly using
SQL?? Some nice clean and beautiful way?

--
Thanks,
Summa

[John Bell]

Hi

Have you thought of using XML for this?

http://sqlxml.org/faqs.aspx?faq=53

You may also want to search Google for hierarchy and SQLServer

If you want a permanent reference you can try the following books:
Advanced Transact-SQL for SQL Server 2000 by Itzik Ben-Gan and Tom Moreau.
The book has an entire chapter on modeling a hierarchical structure in SQL
Server, along with T-SQL to implement the same.

SQL for Smarties by Joe Celko. This has an alternative method of modeling
trees in an RDBMS.

John

"Summasummarum" <Su***********@hotmail.com> wrote in message
news:3f*********************@dtext02.news.tele.dk. ..

Hi,

As subj says. A simple menu is defined in a table. But how to extract it?
Heres the deal:

Table menu has these 3 columns: menuid parent menudesc

Ok this should be easy right? Those menu-rows that has parent=0 is
"toplevel". And so forth. An example:

1 0 "Items"
2 0 "Standings"
3 0 "Test"
4 2 "Liverpool"
5 2 "AC Milan"
6 1 "Itemno1"

This menu should in layout come in this order:

Items
Itemno1
Test
Standings
AC Milan
Liverpool

That is: Alphabetically sorted and with the children underneath the parents. Right? Yes - and selecting the menu and doing some gymnastics in ASP isnt
that hard. But ASP must be the language that has the "crapiest" way of
handling arrays...so a simple task is really messy. Creating a stored
procedure involes 2-3 temporary tables as I see it. Not vey elegant either.
So my question is: Isnt there any way of selecting this order directly using SQL?? Some nice clean and beautiful way?

--
Thanks,
Summa

[Summasummarum]

John Bell wrote:

Hi

Have you thought of using XML for this?

http://sqlxml.org/faqs.aspx?faq=53

You may also want to search Google for hierarchy and SQLServer

Very nice links...thatnks.

/Summa