开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜