I have a database table, part of an exisitng application which cannot be changed I just have to use the table.
The layout of the table is as follows:
What I need to do is have a SQL statement return all the managers with their Employees and then those Employees who are managers with those that report to them, the Employee.
Output needs to be something like this, Level is the level they are in in terms of where they are in reporting:
Level Manager Employee
1 James Bob
1 James Greg
1 James John
2 Bob Ross
2 Bob Jason
2 Greg Alex
3 Ross Philip
3 Ross Brian
I hope this makes sense.
What I need is from one table that has both parents and children return those children of each parent where a child can be a parent and a parent can be a child and the level they are on in terms of reporting.