This is a good problem; let me think out loud.
I need to clean up the DDL in this thread first. In yours, IDENTITY can
never be a key; you allow NULLs everywhere; you allow duplicate rows;
etc. And I made the mistake of making the custom_code column a key.
Arrgh! So this is what the table should look like:
CREATE TABLE NestedTree
(custom_code VARCHAR(20) NOT NULL, --dups allowed
lft INTEGER NOT NULL UNIQUE,
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft BETWEEN 1 AND rgt),
PRIMARY KEY (lft, rgt));
'j3'. I also need the path all the way up to level 'l5' and even deeper
if level 'l5' is not the leaf node. <<
Let me try this again: You want all the paths from the root to the leaf
nodes such that 'j3' is a superior of 'l5' somewhere in that path.
I have been thinking of working from the root down; it looks better to
think of going from leaf nodes up.
SELECT T1.* AS candidate_leaf
FROM NestedTree AS T1, NestedTree AS T2, NestedTree AS T3
WHERE T1.lft = T1.rgt - 1 --start at leaf nodes
AND T1.lft BETWEEN T2.lft AND T2.rgt --T1 inside T2
AND T2.custom_code = 'l5'
AND T2.lft BETWEEN T3.lft AND T3.rgt --T2 inside T3
AND T3.custom_code = 'j3';
This should give you the leaf nodes of the paths in which 'j3' is a
superior to 'l5'. From the leaf nodes, you can easily construct the
paths with the usual BETWEEN predicates.
FROM NestedTree AS T0,
(<<above>>) AS L0
WHERE L0.lft BETWEEN T0.lft AND T0.rgt;
I'd consider displaying this as one path on a line, if the tree is not
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!