How to get Employee Manager Hierarchy In SQL ?

In part two of the SQL Server Questions and Answers series, we will delve into writing SQL queries to retrieve the complete organizational hierarchy based on an employee ID, as well as obtaining all employees along with their respective managers.

The employee table comprises three columns: employee ID, employee name, and manager ID.

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 covered these topics extensively in our SQL Server tutorial. Therefore, if you're unfamiliar with these concepts, I highly recommend reviewing them before continuing with this post. Now, let's define the problem.

When an employee ID is provided to the query, the expected output should display the entire organizational hierarchy. This includes listing the manager of the specified employee ID, their manager's manager, and so forth, until the entire hierarchy is displayed.

To provide context, let's illustrate with a few examples. Below is the employee table followed by the organization hierarchy.

If we examine the data provided, we notice that Anthony holds the highest managerial position, indicated by the absence of any entry in the managerId column, which is set to null for Anthony.

Additionally, Lincoln and Ryan directly report to Anthony, as evidenced by their managerId values, both set to 7, which corresponds to Anthony's employee ID.

Now, based on this dataset, here's what we aim to achieve with the query: if we input Grayson's employee ID, the query should return the following result:

• For Grayson, Lincoln is listed as the manager, as Lincoln directly reports to Anthony.
• Since Lincoln reports to Anthony, Grayson ultimately reports to Anthony, who is the highest-ranking authority in the organization.

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

However, when examining the manager name, it currently displays null because there is no corresponding manager ID. Instead of displaying null, we desire to show "no boss".

To achieve this, there are several approaches available. One method is to utilize the ISNULL function. Let's proceed by executing this method.

Upon execution, we observe that the output meets our expectations, with "no boss" displayed where applicable.

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

This T-SQL script is a recursive Common Table Expression (CTE) used to traverse the hierarchy of employee-manager relationships in a table named TblEmployeeManager. 

Declare and set variable:

@EmpId is declared and set to a specific employee ID (5 in this case).

Common Table Expression (CTE):

• The CTE named OrganizationemployeeCTE is defined. It starts by selecting the EmpId, EmployeeName, and Manager_Id from the TblEmployeeManager table where the EmpId matches the @EmpId variable.
• The UNION ALL clause is used to combine the results of two SELECT statements:
• The first SELECT statement retrieves the details of the employee specified by @EmpId.
• The second SELECT statement recursively selects employees who report to the managers identified in the previous step. This recursive step is achieved by joining the TblEmployeeManager table with the OrganizationemployeeCTE CTE on the Manager_Id column.

Main Query:

• The main query selects EmployeeName from the OrganizationemployeeCTE CTE as emp1.
• It also selects the manager's name (if available) from the OrganizationemployeeCTE CTE as emp2. The ISNULL function is used to handle cases where an employee does not have a manager (in such cases, it displays 'No Boss').
• The LEFT JOIN is used to join emp1 with emp2 based on the Manager_Id and EmpId columns, respectively.

This script retrieves the names of employees and their respective managers for a given employee ID (@EmpId). It recursively traverses the employee-manager hierarchy to determine each employee's manager. If an employee has no manager, it displays 'No Boss'.