Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 2 - SQL query to get organization hierarchy

Suggested Videos:
Part 1 - How to find nth highest salary in sql

To get the best out of this video, the following concepts need to be understood first. These are already discussed in SQL Server Tutorial.
1. Self-Join
2. CTE
3. Recursive CTE



Here is the problem definition:
1. Employees table contains the following columns 
    a) EmployeeId, 
    b) EmployeeName 
    c) ManagerId 
2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed.



Consider the following organization hierarchy chart
SQL query to get organization hierarchy

For example, 
Scenario 1: If we pass David's EmployeeId to the query, then it should display the organization hierarchy as shown below.
organization hierarchy sql server

Scenario 2: If we pass Lara's EmployeeId to the query, then it should display the organization hierarchy as shown below.
organizational hierarchy sql

We will be using the following Employees table for this demo
sql server query to display org chart

SQL to create and populate Employees table with test data
Create table Employees
(
     EmployeeID int primary key identity,
     EmployeeName nvarchar(50),
     ManagerID int foreign key references Employees(EmployeeID)
)
GO

Insert into Employees values ('John', NULL)
Insert into Employees values ('Mark', NULL)
Insert into Employees values ('Steve', NULL)
Insert into Employees values ('Tom', NULL)
Insert into Employees values ('Lara', NULL)
Insert into Employees values ('Simon', NULL)
Insert into Employees values ('David', NULL)
Insert into Employees values ('Ben', NULL)
Insert into Employees values ('Stacy', NULL)
Insert into Employees values ('Sam', NULL)
GO

Update Employees Set ManagerID = 8 Where EmployeeName IN ('Mark', 'Steve', 'Lara')
Update Employees Set ManagerID = 2 Where EmployeeName IN ('Stacy', 'Simon')
Update Employees Set ManagerID = 3 Where EmployeeName IN ('Tom')
Update Employees Set ManagerID = 5 Where EmployeeName IN ('John', 'Sam')
Update Employees Set ManagerID = 4 Where EmployeeName IN ('David')
GO

Here is the SQL that does the job
Declare @ID int ;
Set @ID = 7;

WITH EmployeeCTE AS
(
     Select EmployeeId, EmployeeName, ManagerID
     From Employees
     Where EmployeeId = @ID
    
     UNION ALL
    
     Select Employees.EmployeeId , Employees.EmployeeName,
             Employees.ManagerID
     From Employees
     JOIN EmployeeCTE
     ON Employees.EmployeeId = EmployeeCTE.ManagerID
)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId

sql server interview questions and answers

8 comments:

  1. Can anyone suggest how to add a "Employee Level" column to this? For Example if hierarchy is John - reports to - Laura - reports to - Ben THEN the Levels will be 1 - 2 -3

    ReplyDelete
    Replies
    1. Declare @ID int ;
      Set @ID = 7;
      WITH EmployeeCTE AS
      (
      Select EmployeeId, EmployeeName, ManagerID
      From Employees
      Where EmployeeId = @ID

      UNION ALL

      Select Employees.EmployeeId , Employees.EmployeeName,
      Employees.ManagerID
      From Employees
      JOIN EmployeeCTE
      ON Employees.EmployeeId = EmployeeCTE.ManagerID
      )
      --Select * From Employees EmployeeCTE
      Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName,ROW_NUMBER()over (order by E1.EmployeeId) as LEVEL
      From EmployeeCTE E1
      LEFT Join EmployeeCTE E2
      ON E1.ManagerID = E2.EmployeeId

      Delete
    2. Declare @ID int ;
      Set @ID = 4;
      WITH EmployeeCTE AS
      (
      Select EmployeeId, EmployeeName, ManagerID, 1 as Level
      From Employees
      Where EmployeeId = @ID

      UNION ALL

      Select Employees.EmployeeId , Employees.EmployeeName,
      Employees.ManagerID, EmployeeCTE.Level +1
      From Employees
      JOIN EmployeeCTE
      ON Employees.EmployeeId = EmployeeCTE.ManagerID
      )
      Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName,
      ROW_NUMBER () over (order by E1.Level desc) as Level
      From EmployeeCTE E1
      left Join EmployeeCTE E2
      ON E1.ManagerID = E2.EmployeeId
      order by Level desc

      Delete
  2. Hello, Sir. Please clear me, why should i use CTE for this purpose? In that CTE we have 3 select 2 joins and 1 Union.

    I think i can be done using simple self join.

    SELECT
    Emp1.EmployeeName as EmployeeName,
    IsNull(emp2.employeename,'No Boss') as Manager
    From Employees Emp1
    Left Join Employees Emp2 on Emp.ManagerId = Emp2.EmployeeId

    And please let me know if i am doing somethign wrong?

    ReplyDelete
  3. I am sorry i got i now that why did you use CTE over self join. my query is just selecting the the employee and his manager not the hierarchy!.
    you done well..
    sorry again.

    ReplyDelete
  4. Can we use this in Asp.net MVC Application?

    ReplyDelete
  5. I want to go other way round.
    Suppose I have been given a parent ID and i want to retrieve all the employees ID who are being manged by this manager

    ReplyDelete
    Replies
    1. This gives Top-Bottom Organization Hierarchy

      WITH EmployeeCTE AS
      (
      Select EmployeeId, EmployeeName, ManagerID
      From Employees
      Where EmployeeId = @ID

      UNION ALL

      Select Employees.EmployeeId , Employees.EmployeeName,
      Employees.ManagerID
      From Employees
      JOIN EmployeeCTE
      ON Employees.ManagerID = EmployeeCTE.EmployeeId
      )

      Select E1.EmployeeId, E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
      From EmployeeCTE E1
      LEFT Join Employees E2
      ON E1.ManagerID = E2.EmployeeId

      Delete

It would be great if you can help share these free resources