Re: trees, WITH operator, and traversal

>> referencing mr. celko's "sql for smarties" 2nd; #28.7.3, db2 WITH
operator and fig. 29.2, tree diagram. given data that successive nodes are A, C, H (stored in a temporary table, the context is jdbc so host variable manipulation is not facile); is there a sql statement which returns K, L? all of the examples i've seen on the general subject show how to return all the child nodes from a given node. what i need is to traverse a tree through a given set of node identifiers. <<

I am at work on New Year's Eve day and do not have a copy of my own book here, so I cannot look at the diagram. Using a personnel table, an employee (:my_employee) and all his subordinates is simply:

 SELECT P1.*
   FROM Personnel AS P1, Personnel AS P2   WHERE P1.lft BETWEEN P2.lft AND P2.rgt     AND P2.emp = :my_employee;

To find the immediate subordinates, you can use this query:

 SELECT P1.*
   FROM Personnel AS P1, Personnel AS P2   WHERE P1.lft BETWEEN P2.lft AND P2.rgt     AND P2.emp = :my_employee
    AND NOT EXISTS -- nothing between P1 and P2

        (SELECT *
           FROM Personnel AS P3
          WHERE P3.lft BETWEEN P2.lft AND P2.rgt
            AND P1.lft BETWEEN P3.lft AND P3.rgt
            AND P3.lft NOT IN (P1.lft, P2.lft));