Re: Problem with Nested Sets

1. Buy my book on TREES & HIERARCHIES IN SQL; I need the money and you could use the information :)
2. 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.

Fields are not columns and you have mixed data in one table. This is all you need for the structure table; the nodes will be in another table.

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 = @my_node; Received on Wed Feb 23 2005 - 12:28:02 CST