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
For example,
Scenario 1: If we pass David's EmployeeId to the query, then it should display the organization hierarchy as shown below.
Scenario 2: If we pass Lara's EmployeeId to the query, then it should display the organization hierarchy as shown below.
We will be using the following Employees table for this demo
SQL to create and populate Employees table with test data
Here is the SQL that does the job
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
For example,
Scenario 1: If we pass David's EmployeeId to the query, then it should display the organization hierarchy as shown below.
Scenario 2: If we pass Lara's EmployeeId to the query, then it should display the organization hierarchy as shown below.
We will be using the following Employees table for this demo
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
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
ReplyDeleteDeclare @ID int ;
DeleteSet @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
Declare @ID int ;
DeleteSet @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
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.
ReplyDeleteI 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?
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!.
ReplyDeleteyou done well..
sorry again.
Can we use this in Asp.net MVC Application?
ReplyDeleteI want to go other way round.
ReplyDeleteSuppose I have been given a parent ID and i want to retrieve all the employees ID who are being manged by this manager
This gives Top-Bottom Organization Hierarchy
DeleteWITH 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