Level1, level2, level3.. nth level display of managers of an employee sql-CodePudding

Can someone please let me know how this can be accomplished? I have a table with following fields and data- employeeid, email are unique

what my output should look like for Donnie-

I have to append the hierarchy to each employee row. Suppose employee- don's manager bob has katie as direct manager instead of john then it should get displayed as level1->john, level2->katie,level3->bob, ename->don, direct manager-bob. This could go up to 5 levels. If one of the managers left the job then the employee should get assigned to the prior level manager.

Thanks for your help in advance

CodePudding user response：

First of all, you need to fix your table design so it uses managerid, not managername; at the moment your model doesn't allow for two managers with the same first name.

Secondly, please tag your question with the DBMS you are using.

If you are sure there are only going to be a maximum of 5 levels then just left outer join the table to itself 5 times - each time using the managerid of the current instance of the join joined to the employeeid of the next instance of the join:

SELECT ...
FROM table a
left outer join table b on a.managerid = b.employeeid
left outer join table c on b.managerid = c.employeeid
...

If the hierarchy might change then you ought to be looking at hierarchical queries - but the syntax for these are often DBMS-specific, hence the request for you to tag your question with the DBMS you are using (though you should do this anyway as it's good practice)

CodePudding user response：

 with s (eid, ename, mname, lvl) as 
  (select eid, ename, mname, 1 as lvl 
   from tbl 
   union all 
  select eid, ename, mname, a.lvl 1 
   from s a, 
        tbl b 
   where a.mname=b.ename) 
 select eid,
        max(ename),
        max(mname),
        max(case when lvl=1 then mname end) lv11name,
        max(case when lvl=2 then mname end) lv12name,
        max(case when lvl=3 then mname end) lv13name
     from s ;

This query is working for me. I still have to accommodate the scenario where if manager left the job, I need to update the employee hierarchy to the prior level supervisor. Please let me know how that can be handled in the above sql