Re: hierarchical query - returning a treewalked subset

From: havardk <hrkristiansen007_at_hotmail.com>
Date: Fri, 09 Nov 2007 02:00:40 -0800
Message-ID: <1194602440.974294.209010@o38g2000hse.googlegroups.com>

Ah... Now I understand your problem. Avoiding duplicates complicates matters, because then you will have to traverse the tree the other way to see if any other candidate is listed higher, starting from all rows that satisfy your "start with" criteria.

The select below is even slower, but still works for this problem. I suspect there is an easier way to solve this :-)

"Kevin" will result in two rows, one being the boss for the other, so
"Kevin" the lowlife employee will be excluded from the "start with"-
clause.

Havard

with emp_start as (
select emp2.employee_id /* All potential candidates */ from employees emp2
where emp2.first_name = 'Kevin')
select level, emp.*
from employees emp
start with emp.employee_id in (select es.employee_id /* Valid candidates */

                                from   emp_start es
                                where  1 = (select count(*) /* Only
one candidate up the tree*/
                                                   from   employees
emp3
                                                   start  with
emp3.employee_id = es.employee_id
                                                   connect by prior
emp3.manager_id = emp3.employee_id
                                                   and

emp3.employee_id in (select es2.employee_id  

from emp_start es2

                                                                              )
                                                   )
                               )

connect by prior emp.employee_id = emp.manager_id / Received on Fri Nov 09 2007 - 04:00:40 CST