How to SELECT direct and indirect staff

[Jassm Rahma]

Hi,

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:

1. Marketing Manager
2. Sales Manager
3. Sales Executive 1
4. Sales Executive 2
5. Marketing Coordinator 1
6. Marketing Coordinator 2

How can I do this please?


Thanks,
Jassim