X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 1EAB9CD079 for ; Sun, 20 Jul 2003 07:44:40 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 05398-10 for ; Sun, 20 Jul 2003 07:45:15 +0000 (GMT) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by svr1.postgresql.org (Postfix) with ESMTP id C07C0CBFF1 for ; Sun, 20 Jul 2003 04:44:23 -0300 (ADT) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.9.3/8.9.3) with ESMTP id LAA19226; Sun, 20 Jul 2003 11:44:49 +0400 (MSD) Date: Sun, 20 Jul 2003 11:44:49 +0400 (MSD) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Benjamin Smith Cc: pgsql-sql@postgresql.org Subject: Re: Join table with itself for heirarchial system? In-Reply-To: <200307161834.08104.bens@effortlessis.com> Message-ID: References: <200307161834.08104.bens@effortlessis.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/300 X-Sequence-Number: 14414 You might look on our contrib/ltree module (http://www.sai.msu.su/~megera/postgres/gist/ltree) Oleg On Wed, 16 Jul 2003, Benjamin Smith wrote: > Can you query a set of nested entries to simulate a heirarchial system with a > single query? > > I'm building a nested category table with a definition like below" > > CREATE TABLE category ( > id serial, > parent integer not null, > title varchar); > > Idea is that we can "nest" categories so that we have > > id parent title > ---------------------------------------------- > 1 0 Clothing > 2 1 Shirts > 3 1 Pants > 4 1 Socks > 5 4 Male > 6 4 Silk > > So that, for example, id 6 would be > > Clothing -> Socks -> Silk. > > So far, I've only been able to derive this with 3 queries - 1 to get the > parent for id #6 (Silk) another to get the parent for id #4 (Socks) and > finally for id #1 (Clothing) and since parent ==0 I stop. > > This seems wasteful - can this be done in a single query? > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83