CODEWITHSUNDEEP
sql
Var
Var

Reputation: 23

Sql query to get organisational level hierarchy from one table

I have one table named employee. Please refer to given below table and i want the level in which employee reporting to which manager. I dont know what will be the logic. I have used CTE but I am not getting the expected result.Please help me out in this case for getting the expected result.

Table Employee

EmpID.                     EmpName.                         ManagerID
1                             A                                Null
2                             B                                  1
3                             C                                  1
4                             D                                  2
5                             E                                  3
6                             F                                  4
7                             G                                  5
8                             H                                  5

Expected Result

EmpName.                      ManagerName                         Level
A                                Null                                1
B                                  A                                 2
C                                  A                                 2
D                                  B                                 3
E                                  C                                 4
F                                  D                                 5
G                                  E                                 6
H                                  E                                 6

Upvotes: 2

Views: 8187

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you want a simple join:

select e.name, em.name as manager_name
from employees e left join
     employees em
     on e.managerId = em.empId;

Upvotes: 0

LukStorms
LukStorms

Reputation: 29667

A Recursive CTE works in Postgresql.

And once you got all the levels per employee then it can be grouped to get the maximum level. 

WITH RECURSIVE RCTE AS
( 
    SELECT 
     EmpID AS BaseEmpID, 
     ManagerID AS BaseManagerID,
     1 AS Lvl, 
     EmpID, 
     ManagerID
    FROM employee

    UNION ALL

    SELECT 
     c.BaseEmpID,
     c.BaseManagerID,
     Lvl + 1,
     m.EmpID,
     m.ManagerID
    FROM RCTE c
    JOIN employee m 
      ON m.EmpID = c.ManagerID
)
, EMPLEVELS AS
(
    SELECT 
     BaseEmpID, 
     BaseManagerID, 
     MAX(Lvl) AS Level
    FROM RCTE
    GROUP BY BaseEmpID, BaseManagerID
)
SELECT 
 e.EmpName, 
 m.EmpName AS ManagerName,
 elvl.Level
FROM EMPLEVELS elvl
JOIN employee e ON e.EmpID = elvl.BaseEmpID
LEFT JOIN employee m ON m.EmpID = elvl.BaseManagerID
ORDER BY elvl.BaseEmpID;

Returns: 

empname managername level
A       NULL        1
B       A           2
C       A           2
D       B           3
E       C           3
F       D           4
G       E           4
H       E           4

The same query works in MS Sql Server if the RECURSIVE word is removed.

Upvotes: 0

Amir Molaei
Amir Molaei

Reputation: 3830

Presuming you are using Sql Server, you can do it with the following code: 

WITH cte (EmpId, EmpName, ManagerName, LEVEL) AS (
SELECT EmpId, EmpName, CAST('' AS VARCHAR) as ManagerName, 1 AS LEVEL
FROM Employee
WHERE ManagerId IS NULL

UNION ALL

SELECT e1.EmpId, e1.EmpName, CAST(cte.EmpName AS VARCHAR) ManagerName, (cte.LEVEL + 1) AS LEVEL
FROM Employee e1
    JOIN cte ON e1.ManagerId = cte.EmpId    
)

SELECT EmpName, ManagerName, LEVEL FROM cte
ORDER BY EmpName

Note that you need to revise the levels in your expected output. For example, for employee H the hierarchy is H => E => C => A which shows that it's level is 4. The correct levels are as the following: 

EmpName  ManagerName  Level    
A                     1
B        A            2
C        A            2
D        B            3
E        C            3
F        D            4
G        E            4
H        E            4

Upvotes: 1

Related Questions