oracle recursive query of employees under manager giving manager one level down
I have an employee table that unloops via employeeid = managerid
What I would like to have in my result set is {employeeid, 1-level-down-from-request}
so given
Employeeid - Managerid
Bob - Null
Jill - Bob
John - Bob
Suzy - Jill
Shannon - Jill
Shawn - Jill
Ron - John
Rick - John
Xavier - Shawn
if i ran the query on Bob we'd get
Shannon - Jill
Shawn - Jill
Ron - John
Rick - John
Xavier - Jill
开发者_开发技巧notice that Xavier went to Jill, not Shawn because Jill is Xavier's senior manager (one step below Bob)
The most simplistic approach would probably be to do a self-join
SELECT employee.employeeId,
employee.managerId
FROM table_name mgr,
table_name subordinate,
table_name employee
WHERE mgr.managerId = subordinate.employeeId
AND subordinate.managerId = employee.managerId
AND mgr.employeeId = 'Bob'
It would be more efficient and more general, however, to build a hierarchical query. Something like
SELECT *
FROM (SELECT employeeId, managerId, level lvl
FROM table_name
START WITH employeeId = 'Bob'
CONNECT BY prior employeeId = managerId)
WHERE lvl = 3
Obviously, it would be much easier to change the latter query to change whether you want direct reports or second level reports or some other level of the hierarchy. And it will generally be more efficient as well.
精彩评论