Received: from gateway.heart.com.au (mail.heart.com.au [203.12.235.237]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9TMBQP03295 for ; Mon, 29 Oct 2001 17:11:29 -0500 (EST) (envelope-from keith@heart.com.au) Received: from tardis.heart.com.au (IDENT:root@tardis.heart.com.au [192.168.0.42]) by gateway.heart.com.au (8.11.2/8.11.2) with ESMTP id f9U89ux02450; Tue, 30 Oct 2001 19:09:56 +1100 Received: from heart.com.au ([192.168.0.87]) by tardis.heart.com.au (8.9.3/8.8.7) with ESMTP id JAA17130; Tue, 30 Oct 2001 09:10:41 +1100 Message-ID: <3BDDD39B.89A2A19C@heart.com.au> Date: Tue, 30 Oct 2001 09:09:31 +1100 From: Keith Gray X-Mailer: Mozilla 4.76 [en] (Win98; U) X-Accept-Language: en MIME-Version: 1.0 To: Jason Kwok CC: pgsql-sql@postgresql.org Subject: Re: Recursive select References: <00af01c15deb$74289940$0201a8c0@newhonest.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200110/400 X-Sequence-Number: 5352 Jason Kwok wrote: > > I have a table : > > MyID parentID > ================ > 5 6 > 6 7 > 3 13 > 7 3 > > Is there any simple select statement that can get all rows with MyID = 5 and > all its parents? > That means 5's parenet is 6, 6's parent is 7, 7's parent is 3..... > I had a similar problem a couple of weeks back. The only way I could get around it was "programmatically". This is an iterative (loop) problem. Because I am writing for several brands/flavours of database I solved it in VB, but some solutions (for postgres only) are offered using functions or triggers in PgSQL. -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:keith@heart.com.au