Re: Problem with Nested Sets
Date: 23 Feb 2005 10:28:02 -0800
Message-ID: <1109183282.399830.298840_at_o13g2000cwo.googlegroups.com>
- Buy my book on TREES & HIERARCHIES IN SQL; I need the money and you could use the information :)
- I have a table which represents a tree of forums using nested sets. Here are the fields [sic]: id, root_id, left, right, level, label.
CREATE TABLE Tree
(root_id INTEGER NOT NULL
REFERENCES Nodes(node_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
lft INTEGER NOT NULL UNIQUE,
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft BETWEEN 1 AND rgt));
left and right are reserved words. Three is no such thing as a magical, universal "id" -- it must identify a particular kind of entity. Surely you are not doing IDENTITY as a key!!
The level can be computed from the (lft, rgt) pairs. Since you did not post any DDL or sample I have no idea what label means.
To get all the superiors of a node,use this query:
SELECT O2.*
FROM Tree AS T1, Tree AS T2
WHERE T1.lft BETWEEN T2.lft AND T2.rgt
AND T1.node_id = _at_my_node;
Received on Wed Feb 23 2005 - 19:28:02 CET