Re: [SQL] A path through a tree

"Neil Burrows" <maillist@remo.demon.co.uk> writes:

> 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).

Alas, no.  (I think, anyway.  There wasn't the last time I looked!)
What you want is proposed as RECURSIVE UNION, or RECURSIVE JOIN, or
something.  For reasonably static trees, you can set things up so that
the answer is straightforward: encode leaves with suitable distinct
values, and have "min" and "max" columns which encode the minimum and
maximum values in the subtrees.

There's an FAQ for one of the commercial databases which explains this
(I forget which), and Joe Celko's "SQL for Smarties" also describes
issues like this.