467,166 Members | 1,111 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,166 developers. It's quick & easy.

SQL - a menu system - plz help

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
Jul 20 '05 #1
  • viewed: 1334
Share:
2 Replies
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

Jul 20 '05 #2
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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Mohit Gupta | last post: by
7 posts views Thread by Chuck Hartman | last post: by
2 posts views Thread by Peter Proost | last post: by
3 posts views Thread by John | last post: by
reply views Thread by Patel | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.