Re: SQL a simple menu - plz help

> 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?

You're in need of the "CONNECT BY" option which is ideal for scenarios
such as yours. Unfortunately PostgreSQL doesn't have it yet, but I believe
there is a PLSQL script (or something like this) which emulates the CONNECT
BY behaviour and it's called "connectby()" or something like that.

--
Randolf Richardson - rr(at)8x(dot)ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.