Unanswered: How to SELECT direct and indirect staff
I have a Marketing & Sales Director, the Marketing & Sales Director has one Marketing Manager and Sales Manager reporting to him. The Sales Manager has two Sales Executives and the Marketing Manager has two Marketing coordinators reporting to them.
I can make a SELECT to list staff reporting to the Sales Manager and staff reporting to the Marketing Manager.
I can also make a SELECT to list staff reporting to the Marketing & Sales Director (which are Marketing Manager and Sales Manager).
Now I want to make a SELECT to list all staff reporting directly on indirectly to the Marketing & Sales Director including staff reporting to managers under the Marketing & Sales Director so the query should list the below when I query for staff reporting to the Marketing & Sales Director:
can you give us your table layout? Just from my point of view, I would think you query the table getting the employees of your Marketing & Sales Director, then left outer join to the table getting the staff of those people if they exist. This approach, however, wouldn't catch staff of the next level of subordinates. Also, you'd have to play with it as using just a generic outer join, the second level of employees would just be another column and the immediate staff would be listed multiple times. Similar to:
then like I said the easiest way is to left outer join to the table to get first level of subordinates. This will give you a start and you can take a look at recursion to get further down levels of staff. To start you off try something along these lines:
select a.last_name, c.last_name
from employees a
inner join employee_job b
on b.employee_number = a.employee_number
and b.reporting_to = ???
left outer join employee_job d
on a.employee_number = d.reporting_to
inner join employees c
on d.employee_number = c.employee_number