Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Return the managers and Employees that reports to a manager

916212Feb 8 2012 — edited Feb 10 2012
Hello Everyone,
I need your help in the following query using connect by:

I have 2 tabels
Tabel_1

Department hierarchy

department_id parent_department_id has_manager
1 NULL X
2 1
3 1 X
4 2 X
5 4


tabel_2

emp_id department_id is_manager
1 1 X
2 1
3 1
4 2
5 2
6 4 X
7 4
8 4
9 3 X
10 5 X
now in my company the concept of direct employees is as follow:

my direct employees are: the employees in my department + only the manager in the department below me in the hierarchy.

if the department bellow me has no manager then also these employees in that department reports to me
then we check the lower department and so on untill we find a manger then this manager reports to me but his employees reports to him so they are not my direct employees

for the above example should be as follows

if i want to get the direct employees of emp_id= 1

then result is
2
3
4
5
6
9


Best regards
Ibrahim
This post has been answered by Frank Kulash on Feb 8 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 9 2012
Added on Feb 8 2012
18 comments
938 views