Am Freitag, 13. Mai 2005 10.32 schrieb Marcus Bointon:
> I have a table that represents a tree structure via a self-join. I'd
> like to get hold of all parent records in a single query - is such a
> thing possible? e.g. given
>
> id parentid
> 1 0
> 2 1
> 3 2
> 4 2
> 5 1
> 6 4
There is an alternative way of modelling tree structures in a relational db,
"nested sets", it's more complex, but your requirements (and others) can be
reached with a single statement.
I'm just evaluating this thing, so I have no experience with it, but it sounds
very good.
Here is a link:
http://www.intelligententerprise.com/001020/celko1_1.jhtml
There are also perl modules (where you could get statements for specific
task):
http://search.cpan.org/~djcp/DBIx-Tree-NestedSet-0.16/lib/DBIx/Tree/NestedSet.pm
===
from the first link:
CREATE TABLE Personnel
(emp CHAR(10) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
1. Find an employee and all his/her supervisors, no matter how deep the tree.
SELECT P2.*
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = :myemployee;
joe
> If I was starting with record 4, I would want it to return records 2
> and 1 (probably in that order), starting from 5 would just give me 1
> etc. It needs to support arbitrary depth, hence the need for recursion.
>
> Can I do this in one go, or do I have to query iteratively until I
> encounter a zero reference?
>
> Marcus
> --
> Marcus Bointon
> Synchromedia Limited: Putting you in the picture
> marcus@stripped | http://www.synchromedia.co.uk
