Received: from mail.vicnet.net.au (merri.vicnet.net.au [203.10.72.9]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA83879 for ; Sat, 27 Nov 1999 18:00:44 -0500 (EST) (envelope-from julienc@vicnet.net.au) Received: from jcadiou (dhcp76-72.slv.vic.gov.au [202.137.76.72]) by mail.vicnet.net.au (Postfix) with SMTP id 15239F8136 for ; Sun, 28 Nov 1999 10:00:42 +1100 (EST) Message-Id: <3.0.3.32.19991128100041.0155db00@mail.vicnet.net.au> X-Sender: julienc@mail.vicnet.net.au X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.3 (32) Date: Sun, 28 Nov 1999 10:00:41 +1100 To: pgsql-sql@postgreSQL.org From: Julien Cadiou Subject: recusrion Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Hi, I'm doing a portal and have a recursion problem. I just need advice on whether or not I'm wasting my time in thinking I can do what I need with SQL. We're basically building a yahoo-like portal. My categories table is as follows this email. Each category has a primary key and an owner (the owner is the primary key of the category owning that category). I want to extract a site map in one query: ie: extract the cetagory and its subcategories if any and any of the subcategories' subcategories if any etc .... Right now, in failure to do so with SQL, I'm selecting the whole thing in a hash array in perl and reorganising it (which is fine, it worksm but if it can be "cleaner", that's better !), but I was wondering if I should continue looking for the SQL answer ... I've seen similar things done, but somehow it's slightly different to this case and I can't seem to see it ! I've written a few functions that lead me nowhere, performed self joins etc ... but I think I'm looking at it from the wrong angle ... any suggestions ? Thanks. id|owner|name --+-----+------------------------- 2| 0|Business 9| 7|How to learn 12| 9|Good schools 13| 12|Good schools in Melbourne 14| 13|Good schools in Carlton 16| 6|Victorian Private Banks 17| 1|Barbeques 18| 17|Victorian barbeques 3| 1|Sports 4| 2|Banking 5| 3|Tennis 6| 4|Victorian Banks 7| 5|Lessons 8| 2|Finance 1| 0|Outdoors 0| 0|Home 19| 5|Tennis Clubs 20| 19|Melbourne CLubs 21| 5|Tournaments 22| 13|Free tuition