SQL select - self join

[JBDev]

I’m trying to create a self join in a table that selects specific records as it cascades through a record chain. Each employee record contains a field “ReportsTo” which contains the employee id of the manager each employee reports to. All levels of management are contained in this file. Here’s a sample scenario:

I want to select all employees that report to a particular VP:

Rec No. Emp No ReportsTo
1 3 (null)
2 33 3
3 241 33
4 252 33
5 342 44
6 324 33

When I run the Access query specifying Employee #3 – the returned records should be: 2, 3, 4 and 6.

Any help is greatly appreciated!!!

Thank you,
- JB

 

[r937]

"When I run the Access query specifying Employee #3 – the returned records should be: 2, 3, 4 and 6"

you must have meant to say "specifying employee #33"

select empno
from employees
where reportsto = '33'
or empno = '33'

if the returned records must include the veep himself (who actually reports to the pres), then that's what the OR is for

rudy

http://rudy.ca/

 

[JBDev]

Hi,

Thanks for the input - this would work if the number of levels was always 3, but this can vary. I can only specify the top level record (ie #3) - the query has to find all records that cascade below this for any number of levels. I should have added one more level to my example to better illustrate my dilemma...

new rec 7, emp no 450, rptsto: 252 (who reports to 33 who reports to 3).

The results would then be: 2, 3, 4, 6, 7

I've tried a number of different scenarios - I suppose I could read all records in the table and follow each "reportsTo" to the highest level and see if the empid = 3 (saving all the records until the VP rec is found - and then storing or clearing them for the query based on whether or not it is a match (but I'm sure theres a less processing intensive solution).

here's what the processing should do:

Select emprec from empfile where rptsto = "3"
(finds record 2)
select emprec from empfile where rptsto = "33"
(finds records 3 4 6)
select emprec from empfile where rptsto = "252"
(finds record 7)
....and so on (and number of levels) until eof

- Janice

 

[r937]

ok, i see now what you're after

as far as i know, no database other than oracle has implemented recursion, and oracle's is proprietary syntax

recursion might be defined in sql-99 (offhand, i don't remember) but this is moot if recursion is not supported by your database

you can either write a multi-level outer self join (i.e., join the table to itself n times, where n is the number of levels you want to go down), or else write a procedure to call the database in a loop

how big is n? what's the maximum depth of the reporting hierarchy?

 

[angiole]

Here's something you can try. Let's assume you have a finite number of levels that a hierarchy can go down (this example uses 4, you can do the following:

Given this structure:

Name     Type
-------- ----
EMP1         NUMBER
REPORTS2     NUMBER

With this data:

      EMP1   REPORTS2
---------- ----------
         1
         2          1
         3          1
         4          2
         5          2
       252          2
         6          3
       253        252
       444        252
       555        253

This query will provide the following:

select 	l1.reports2,
	l1.emp1 emp_l1,
	l2.emp1 emp_l2,
	l3.emp1 emp_l3,
	l4.emp1 emp_l4
from 	aa_owner l1,
	aa_owner l2,
	aa_owner l3,
	aa_owner l4
where	l1.emp1 = l2.reports2(+) and
	l2.emp1 = l3.reports2(+) and
	l3.emp1 = l4.reports2(+) 
order by 1,2,3,4

  REPORTS2     EMP_L1     EMP_L2     EMP_L3     EMP_L4
---------- ---------- ---------- ---------- ----------
                    1          2          4
                    1          2          5
                    1          2        252        253
                    1          2        252        444
                    1          3          6
         1          2          4
         1          2          5
         1          2        252        253        555
         1          2        252        444
         1          3          6
         2          4
         2          5
         2        252        253        555
         2        252        444
         3          6
       252        253        555
       252        444
       253        555

I'm not going to pretend this is the last solution possible, but it may help you in the meantime.

You'll notice that you can insert a WHERE statement to select a particular REPORTS2 level, showing the entire tree for that level of management.

Cheers.

p.s. the (+) is Oracle notation for left outer join.

AA 8~)

 

[r937]

select ...
  from aa_owner l1
left outer
  join aa_owner l2
    on l1.emp1 = l2.reports2
left outer
  join aa_owner l3
    on l2.emp1 = l3.reports2
left outer
  join aa_owner l4
    on l3.emp1 = l4.reports2

it ain't that hard, angiole -- this is the ansi sql forum after all

 

[r937]

another way to approach this is with UNIONS of inner joins

JB wanted to list everybody who is "under" a certain employee (including that employee), but presumably just wants each person only once

using inner joins, again with the example of 4 levels...

    select l1.emp1     as empno
         , 1           as emplevel
      from aa_owner l1
     where l1.emp1 = 33
union all
    select l2.emp1 
         , 2 
      from aa_owner l1
    inner
      join aa_owner l2
        on l1.emp1 = l2.reports2
     where l1.emp1 = 33
union all
    select l3.emp1 
         , 3 
      from aa_owner l1
    inner
      join aa_owner l2
        on l1.emp1 = l2.reports2
    inner
      join aa_owner l3
        on l2.emp1 = l3.reports2
     where l1.emp1 = 33
union all
    select l4.emp1 
         , 4 
      from aa_owner l1
    inner
      join aa_owner l2
        on l1.emp1 = l2.reports2
    inner
      join aa_owner l3
        on l2.emp1 = l3.reports2
    inner
      join aa_owner l4
        on l3.emp1 = l4.reports2
    where l1.emp1 = 33
order by 2, 1

results:

empno emplevel
33 1
241 2
252 2
324 2
450 3

note UNION ALL to avoid the sort to detect duplicates, since each select returns a different value for emplevel

note ORDER BY employee number would, in a real example of a department roster, probably be by employee name, and probably not include their level

if emplevel is not included, the UNION ALL is still okay, since presumably the same employee is in each departmet only once

rudy

 

[JBDev]

Wow - thanks to everyone for the helpful suggestions. I'm a bit rusty with SQL but getting back up to speed quickly. I'll try these solutions and see how it goes.

- JB