Re: How to SELECT direct and indirect staff

[Phillip Ward]

The trick here is keeping track of who reports to who.

Assigning a unique identifier to each person:

select * 
from people ; 

+----+------------------------------+ 
| id | Person                       | 
+----+------------------------------+ 
| 11 | Marketing and Sales Director | 
| 22 | Sales Manager                | 
| 33 | Sales Executive 1            | 
| 44 | Sales Executive 2            | 
| 55 | Marketing Manager            | 
| 66 | Marketing Coordinator 1      | 
| 77 | Marketing Coordinator 2      | 
+----+------------------------------+

A new table links each person and who reports to them (you might be able to do this in just the one table, but this gives you far more flexibility).

select * 
from reporting_line 
order by 1, 2 ; 

+----+-----------+ 
| id | report_id | 
+----+-----------+ 
| 11 |        22 | 
| 11 |        55 | 
| 22 |        33 | 
| 22 |        44 | 
| 55 |        66 | 
| 55 |        77 | 
+----+-----------+

Now, you can retrieve the structure, by linking from people to reporting_line and back to people. Each time you do this, you get another level of the hierarchy (given that you only have three levels, this will be sufficient; if you want to nest the structure more deeply, then things get a bit more tricky.

select lvl0.id 
, lvl0.person 
, lvl1.id 
, lvl1.person 
, lvl2.id 
, lvl2.person
from       people lvl0 
inner join reporting_line rl01 
      on   lvl0.id = rl01.id 
inner join people lvl1 
      on   rl01.report_id = lvl1.id 
inner join reporting_line rl12 
      on   lvl1.id = rl12.id 
inner join people lvl2 
      on   rl12.report_id = lvl2.id 
order by lvl0.id 
,        lvl1.id 
,        lvl2.id 
; 

+----+------------------------------+----+-------------------+----+-------------------------+ 
| id | person                       | id | person            | id | person                  | 
+----+------------------------------+----+-------------------+----+-------------------------+ 
| 11 | Marketing and Sales Director | 22 | Sales Manager     | 33 | Sales Executive 1       | 
| 11 | Marketing and Sales Director | 22 | Sales Manager     | 44 | Sales Executive 2       | 
| 11 | Marketing and Sales Director | 55 | Marketing Manager | 66 | Marketing Coordinator 1 |
| 11 | Marketing and Sales Director | 55 | Marketing Manager | 66 | Marketing Coordinator 1 |
+----+------------------------------+----+-------------------+----+-------------------------+

Regards, Phill W.