How to get Employee manager hierarchy in Sql ?

In this part two of the SQL Server  and Answers post series, we will discuss writing a sql query to get the complete organizational hierarchy based on an employeeId and How to get the All employees with their managers.

The employee table has three columns, employee ID, employee name, and managerId.

Table Script

CREATE TABLE [dbo].[TblEmployeeManager](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [nvarchar](max) NULL,
    [Manager_Id] [int] NULL,
 CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED 
(
    [EmpId] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON 
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF
GO

Sql Query to get employee manager name Using Self Join

Sql Query

SELECT emp.EmpId EmployeeId, emp.EmployeeName EmployeeName,  
       emp.Manager_Id ManagerId, ISNULL(mng.EmployeeName, 'No Boss') AS ManagerName 
FROM   TblEmployeeManager emp 
       LEFT JOIN TblEmployeeManager mng 
       ON emp.Manager_Id = mng.EmpId

SQL query to get Employee Manager Hierarchy

To get the best out of this post. These concepts should be first understood.

• Self-join Basic Concept
• CTE and recursive CTE

We discussed these in detail in our SQL server tutorial. So if you are new to these concepts, I strongly recommend reading them first before proceeding with this post. So here’s the problem definition.

Now, if an employeeId is passed to the query, then that query should list down the entire organization hierarchy.

That is who is the manager of the employeeID that is passed and who is the manager’s manager and so on, till the full hierarchy is listed.

Let me give it a context with a few examples. So this the employee’s table and this is the organization hierarchy.

Notice that Anthony is our super boss, meaning he doesn’t have any manager. So the managerId column for Anthony is null.

And if you look at Lincoln and Ryan, the two reports directly to Anthony, meaning their manager ID column should have the employeeId of Anthony. So if you look at Lincoln and Ryan, notice the manager ID is 7, which is the employeeId of Anthony.

So now based on this table data. This is what we want to achieve for the query that we are going to write if we pass Grayson’s employee Id?.

So if we pass Grayson employee ID, then the query should return data, as you can see here, Notice that employee name and manager name. So about Grayson, we have Lincoln, Lincoln reports to Anthony. And Anthony is super boss.

So Grayson reports to Lincoln. Lincoln reports to Anthony and Anthony is our super boss.

Sql Query

Declare @EmpId int ;
Set @EmpId = 5;

WITH OrganizationemployeeCTE AS
(
     Select EmpId, EmployeeName, Manager_Id
     From TblEmployeeManager
     Where EmpId = @EmpId
     UNION ALL
     Select TblEmployeeManager.EmpId , TblEmployeeManager.EmployeeName,
            TblEmployeeManager.Manager_Id
     From TblEmployeeManager
     JOIN OrganizationemployeeCTE
     ON TblEmployeeManager.EmpId = OrganizationemployeeCTE.Manager_Id
)

Select emp1.EmployeeName, emp2.EmployeeName as ManagerName
From OrganizationemployeeCTE emp1
LEFT Join OrganizationemployeeCTE emp2
ON emp1.Manager_Id = emp2.EmpId

But look at the manager name. It’s displaying null because it doesn’t have a manager ID. But instead of that, we want this “no bass”.

And how are we going to achieve that? There are several ways, again, to do that. You can either use Coalesce function or ISNULL function. So I’m going to basically use ISNULL function.

so let’s go and execute this, so notice that we get the output that we expect.

Declare @EmpId int ;
Set @EmpId = 5;

WITH OrganizationemployeeCTE AS
(
     Select EmpId, EmployeeName, Manager_Id
     From TblEmployeeManager
     Where EmpId = @EmpId
     UNION ALL
     Select TblEmployeeManager.EmpId , TblEmployeeManager.EmployeeName,
            TblEmployeeManager.Manager_Id
     From TblEmployeeManager
     JOIN OrganizationemployeeCTE
     ON TblEmployeeManager.EmpId = OrganizationemployeeCTE.Manager_Id
)
Select emp1.EmployeeName, ISNULL(emp2.EmployeeName, 'No Boss') as ManagerName
From OrganizationemployeeCTE emp1
LEFT Join OrganizationemployeeCTE emp2
ON emp1.Manager_Id = emp2.EmpId

The post How to get Employee manager hierarchy in Sql ? appeared first on Software Development | Programming Tutorials.