Re: Nested sets question...

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 12 Dec 2004 09:39:07 -0800
Message-ID: <1102873147.892447.242260_at_z14g2000cwz.googlegroups.com>

>> How to find all children in nested sets model that are two levels
under specified item? <<

SELECT T3.node_name AS grandparent,
MIN(T2.node_name) AS parent,
T1.node_name
FROM Tree AS T1, Tree AS T2, Tree AS T3
WHERE T1.node_name NOT IN (T2.node_name, T3.node_name)

AND T2.node_name <> T3.node_name
AND T1.lft BETWEEN T2.lft AND T2.rgt
AND T2.lft BETWEEN T3.lft AND T3.rgt

GROUP BY T1.node_name, T3.node_name
HAVING COUNT(T2.lft) = 1

The idea is to use the GROUP BY to be sure three is only one parent between the grandparent and the node. Here is the version for immediate superiors. It will return a NULL as the parent of the root.

SELECT B.node_name AS parent, E.node_name FROM Tree AS E
LEFT OUTER JOIN
Tree AS B
ON B.lft
= (SELECT MAX(lft)
FROM Tree AS S
WHERE E.lft > S.lft

                AND E.lft < S.rgt);

Received on Sun Dec 12 2004 - 18:39:07 CET