Received: from sardinia.gssec.bt.co.uk (sardinia.gssec.bt.co.uk [132.146.39.103]) by hub.org (8.9.1/8.9.1) with ESMTP id JAA17414 for ; Tue, 12 Jan 1999 09:35:46 -0500 (EST) (envelope-from maillist@remo.demon.co.uk) Received: from dhcpw198.gssec.bt.co.uk ([132.146.203.198]:4599 "HELO towhee" ident: "IDENT-NOT-QUERIED") by sardinia.gssec.bt.co.uk with SMTP id <936705-285>; Tue, 12 Jan 1999 14:27:53 +0000 From: "Neil Burrows" To: Subject: A path through a tree Date: Tue, 12 Jan 1999 14:34:16 -0000 Message-ID: <000c01be3e38$a1d4a170$c6cb9284@towhee.gssec.bt.co.uk> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4 Importance: Normal Hi, OK, I have a feeling that this not something that can be done with SQL but I may as well give it a shot. Say you have a table with the following columns: id int4 NOT NULL UNIQUE parent int4 value varchar(8) and each entry represents a node in a tree. So the top most node will have no parent, and the next nodes will have the 1st node's id as their parent etc etc etc. If I have a leaf node, is there a SELECT statement that will give me all the parent ids on the way to the root? (See diagram below for a different [probably not better] description). The tree can be of arbitrary depth. where i = id and p = parent i = 1 p = NULL | | +-----+-----+ | | i = 2 i = 3 p = 1 p = 1 | | +-----+-----+ | | i = 4 i = 5 p = 3 p = 3 So if I wanted to find all the parent ids from node with index 5 to root I'd get (3,1)? As I say, I doubt there is a simple select that can do this but thought I may as well ask. Thanks in advance, Neil Burrows