Re: Nested sets question...

From: John Gilson <jag_at_acm.org>
Date: Fri, 26 Nov 2004 02:22:29 GMT
Message-ID: <Frwpd.27350$Vk6.5590_at_twister.nyc.rr.com>

"Brundek" <br_at_net.hr> wrote in message news:cnuvap$o82$1_at_ls219.htnet.hr...
> "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?
> >
>
> And another question...
> How to get path (like in enumerated paths model) to root item in nested set
> model? :)

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 Ancestors (node_name, ancestor_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

CREATE VIEW Levels (node_name, level_number) AS
SELECT T1.node_name, COUNT(*)
FROM Tree AS T1

            INNER JOIN
            Tree AS T2
            ON T2.lft <= T1.lft AND
                   T2.rgt >= T1.rgt

GROUP BY T1.node_name

SELECT A.node_name, A.ancestor_node_name,

               L.level_number AS ancestor_level_number FROM Ancestors AS A

            INNER JOIN
            Levels AS L
            ON L.node_name = A.ancestor_node_name

ORDER BY A.node_name, L.level_number

node_name ancestor_node_name ancestor_level_number B A 1
C A 1
D A 1
D C 2
E A 1
E C 2
E D 3
F A 1
F C 2
F D 3
G A 1
G C 2
H A 1
H C 2
H G 3
I A 1
I C 2

--
JAG

Received on Fri Nov 26 2004 - 03:22:29 CET