Re: Nested sets question...

"Brundek" <br_at_net.hr> wrote in message news:cnuuq7$nde$1_at_ls219.htnet.hr...
> How to find all children in nested sets model that are two levels under
> specified item?

CREATE TABLE Tree
(
node_name VARCHAR(10) NOT NULL PRIMARY KEY, lft INT NOT NULL UNIQUE,
rgt INT NOT NULL UNIQUE,
CHECK (rgt > lft)
)

INSERT INTO Tree (node_name, lft, rgt)
VALUES ('A', 1, 18)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('B', 2, 3)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('C', 4, 17)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('D', 5, 10)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('E', 6, 7)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('F', 8, 9)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('G', 11, 14)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('H', 12, 13)
INSERT INTO Tree (node_name, lft, rgt)
VALUES ('I', 15, 16) CREATE VIEW Children (node_name, child_node_name) AS
SELECT T1.node_name, T2.node_name
FROM Tree AS T1

            INNER JOIN
            Tree AS T2
            ON T2.lft > T1.lft AND
                   T2.rgt < T1.rgt AND
                   NOT EXISTS (SELECT *
                                           FROM Tree AS T3
                                           WHERE T3.lft > T1.lft AND
                                                          T3.lft < T2.lft AND
                                                          T3.rgt < T1.rgt AND
                                                          T3.rgt > T2.rgt)

• Two levels down from a node are the children of its children, i.e.,
• its grandchildren CREATE VIEW Grandchildren (node_name, grandchild_node_name) AS SELECT C1.node_name, C2.child_node_name FROM Children AS C1 INNER JOIN Children AS C2 ON C1.child_node_name = C2.node_name

SELECT node_name, grandchild_node_name
FROM Grandchildren
ORDER BY node_name, grandchild_node_name

node_name grandchild_node_name
A D
A G
A I
C E
C F
C H

--
JAG