Re: Need help with hierarchical query

From: Phil Bewig <pbewig_at_swbell.net>
Date: 26 May 2004 05:58:54 -0700
Message-ID: <455f7154.0405260458.744da91@posting.google.com>

Amazing. Many thanks. Your solution is easy to understand and quick to execute. It is exactly what I was looking for.

I did manage to write a solution based on nested sub-queries. I've attached it to the end of this message, but it is big and ugly.

Thanks again,

Phil

select employee_id as eid,

       last_name,
       employee_id as id1,
       null as id2,
       null as id3,
       null as id4

from employees
where manager_id is null
union all
select e2.employee_id as eid,

       e2.last_name,
       id1,
       employee_id as id2,
       null as id3,
       null as id4

from employees e2
join (select employee_id as eid,

               last_name,
               employee_id as id1
        from   employees
        where  manager_id is null) e1
on     e2.manager_id = e1.eid

union all
select e3.employee_id as eid,

       e3.last_name,
       id1,
       id2,
       employee_id as id3,
       null as id4

from employees e3
join (select e2.employee_id as eid,

               e2.last_name,
               id1,
               employee_id as id2
        from   employees e2
        join   (select employee_id as eid,
                       last_name,
                       employee_id as id1
                from   employees
                where  manager_id is null) e1
        on      e2.manager_id = e1.id1) e2
on     e3.manager_id = e2.id2

union all
select e4.employee_id as eid,

       e4.last_name,
       id1,
       id2,
       id3,
       employee_id as id4

from employees e4
join (select e3.employee_id as eid,

               e3.last_name,
               id1,
               id2,
               employee_id as id3
        from   employees e3
        join   (select e2.employee_id as eid,
                       e2.last_name,
                       id1,
                       employee_id as id2
                from   employees e2
                join   (select employee_id as eid,
                               last_name,
                               employee_id as id1
                        from   employees
                        where  manager_id is null) e1
                on     e2.manager_id = e1.id1) e2
        on      e3.manager_id = e2.id2) e3
on      e4.manager_id = e3.id3

order by eid Received on Wed May 26 2004 - 07:58:54 CDT