Querying a table and returning a heirarchy
Posted on 2007-10-10
Business Goal: Management has asked us to return the listing of everyone name who is currently reports to them and everyone under them and so and so forth.
Currently, we have an Oracle database containing a table named as PA_STUDENT. This PA_STUDENT table contains all the employees who work in our organization in column A and the name of their supervisor in column B.
What I need to get from PA_STUDENT is for example:
If I query the name of the Director, then I should return all the directors subordinates and the subordinates' subordinates. If the Director had Joe, Michael, and Dave working under him as group leaders, and Joe, Michael, and Dave have 6 people working under of them, and those 6 people had more people working under each of them and so and so forth, I would like to see all those names come up.
I remember that when I used to take C/C++ classes, we learned the concept of "binary search" trees where 1 parent has 2 child nodes. In this case, we have parents who have multiple children (the supervisors direct reports) and those children also have multiple children.
I hope this makes sense. How can I get this accomplished assuming that I have PA_STUDENT as the table using SQL to grab this information? Please advise.