X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 4220FD1B4E1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 23 Nov 2003 17:21:29 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 44052-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 23 Nov 2003 13:20:58 -0400 (AST)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by svr1.postgresql.org (Postfix) with ESMTP id F28E9D1D5E3
	for <pgsql-sql@postgresql.org>; Sun, 23 Nov 2003 13:20:56 -0400 (AST)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.12.9/8.12.9) with ESMTP id hANHKuNu041530
	for <pgsql-sql@postgresql.org>; Sun, 23 Nov 2003 17:20:56 GMT
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.12.9/8.12.9/Submit) id hANGpESp036595
	for pgsql-sql@postgresql.org; Sun, 23 Nov 2003 16:51:14 GMT
From: "Randolf Richardson, DevNet SysOp 29" <rr@8x.ca>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Re: SQL a simple menu - plz help
Date: Sun, 23 Nov 2003 16:51:14 +0000 (UTC)
Organization: Inter-Corporate Computer & Network Services, Inc.
Lines: 48
Message-ID: <Xns943C5804EB151rr8xca@200.46.204.72>
References: <3fc07554$0$171$edfadb0f@dtext02.news.tele.dk>
X-Complaints-To: usenet@news.hub.org
User-Agent: Xnews/5.04.25
X-Face: 
 +kXh]<cm0}l<Fvzd*?E]I7=Ug!X`>8'{:R`HzXla-aT~$s8a+C)k/B%RBr$_3sP`[kA}cl7#mD^9Z:oM`HpoC_kUEH['RZ*Ob%pz\ww^q&x)~zc`.xd]VQ4WN/3t2nS-<I>BvI%LF\B4:\9$EI:/|<)`?8I_;
	xzG@SOYFir/gk_eB4"Rl43-h%)8O0sR$b&Mp3XHR(0j=
X-Face-Author: Randolf Richardson (composed with Adobe Photoshop)
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/316
X-Sequence-Number: 16131

> 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@8x.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.