5

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.

giannis christofakis
  • 8,201
  • 4
  • 54
  • 65
seesee
  • 1,145
  • 5
  • 20
  • 46

3 Answers3

7

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

GWu
  • 2,767
  • 18
  • 28
  • I tried this before but it won't work when your given node is the actual NODE. Example the node is 100 – seesee Jul 24 '12 at 06:30
  • @seesee: adapted answer to include given node as potential root node, that ok now? (If yes, please mark as answered so that others can see) – GWu Jul 24 '12 at 07:08
  • is the correct answer, I'm currently doing the same BUT there are 3 query.. it will be quite inefficient. – seesee Jul 24 '12 at 09:29
  • actually this solution is a no go because it assume any node it can't find as the root. so the node might not even exist in the tree. I'm starting to have a bad feeling about this requirement. I most likely have to break it into 2 query. – seesee Jul 24 '12 at 17:13
  • @seesee - I don't I think I understand what you mean by "it assume any node it can't find as the root". If the given node does not exist, it just returns no rows (change 100 to 999 in the example and you get no rows). – GWu Jul 24 '12 at 18:11
6

Why not just:

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

Here is a fiddle

EDIT
Here is another try (After understanding the full problem):

with t as (
select case when mgr.employee_id is null then
1 else 0 end is_root, emp.employee_id employee, emp.manager_id manager, emp.last_name last_name

from employees mgr right outer join employees emp
on mgr.employee_id = emp.manager_id
),
tmp as (

select level, employee, last_name, manager ,
connect_by_root manager as root_id,
manager||sys_connect_by_path(employee,
',') cbp

from t
connect by prior employee = manager
start with t.is_root =
1 )
select * from tmp
where tmp.root_id in (select root_id from tmp where employee= 101 or manager = 101)

I checked it with 100, 101 and 1010 and it worked well
Here is a fiddle

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • 1
    What's the point in simply changing the literal to get the required result? The answer of "*How* to calculate the sum of 1 to 10" isn't 55 but the algorithm. – GWu Jul 24 '12 at 06:27
  • How do I start with 100 when I don't know the root when giving 101? – seesee Jul 24 '12 at 06:27
  • @GWu- I see what you mean, but before OP added *"When given '101' as the node, you won't know which is the root"* I thought his problem is that there's no "employee_id=100" record – A.B.Cade Jul 24 '12 at 07:28
1
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 in  ( 
  select employee_id from employees 
  where manager_id is null )
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
DARK LORD
  • 19
  • 1