Find the entire tree from the root giving any node

Question

How do I find the entire tree given a node of a tree?

Example of tree:

       100
  101        102
1010 1011   1020  1021


select level, employee_id, last_name, manager_id ,
       connect_by_root employee_id as root_id
  from employees
 connect by prior employee_id = manager_id
 start with employee_id = 101
;

The root in table is (parent,child) example (100,101) there is no (null,100) row in table.

The above query only gives the children starting from 101. But lets say I want everything from the start of the root?

When given '101' as the node, you won't know which is the root.

The query should be usable when the root is the given node.

Answer 1

You need to first traverse up the tree to get all managers then traverse down to fetch all employees:

select level, employee_id, last_name, manager_id ,
       connect_by_root employee_id as root_id
   from employees
connect by prior employee_id = manager_id -- down the tree
start with manager_id in ( -- list up the tree
     select manager_id 
       from employees
     connect by employee_id = prior manager_id -- up the tree
     start with employee_id = 101
     )
;

See http://www.sqlfiddle.com/#!4/d15e7/18

Edit:

If the given node might also be the root node, extend the query to include the given node in the list of parent nodes:

Example for non-root node:

select distinct employee_id, last_name, manager_id 
   from employees
connect by prior employee_id = manager_id -- down the tree
start with manager_id in ( -- list up the tree
     select manager_id 
       from employees
     connect by employee_id = prior manager_id -- up the tree
     start with employee_id = 101
     union 
     select manager_id -- in case we are the root node
       from employees
     where manager_id = 101
     )
;

Example for root node:

select distinct employee_id, last_name, manager_id 
   from employees
connect by prior employee_id = manager_id -- down the tree
start with manager_id in ( -- list up the tree
     select manager_id 
       from employees
     connect by employee_id = prior manager_id -- up the tree
     start with employee_id = 100
     union 
     select manager_id -- in case we are the root node
       from employees
     where manager_id = 100
     )
;

Fiddle at http://www.sqlfiddle.com/#!4/d15e7/32