Get the list of all subordinates of the employee (Hierarchy of the employee) in the organization

We get a requirement that, we need to create a script to get the list all subordinates of the employee. Subordinates are people who work under someone else in an organization and report to a senior or manager.

Subordinates = employees at a lower level in the hierarchy

Let’s set up for the demo

“Our organization looks like this: CEO at the top, then CTO and CFO, then managers, and finally developers and engineers under them.”

Creating an employee table with self-reference foreign key.

CREATE TABLE Employee (     EmpID   INT IDENTITY(1,1) PRIMARY KEY,     EmpName VARCHAR(100) NOT NULL,     JobTitle     VARCHAR(50),     Department   VARCHAR(50),     ManagerID    INT NULL,     HireDate     DATE,     Salary       DECIMAL(10,2),      CONSTRAINT FK_Employee_Manager         FOREIGN KEY (ManagerID)         REFERENCES Employee(EmpID) );  --Inserting few records  -- CEO INSERT INTO Employee (EmpName, JobTitle, Department, ManagerID, HireDate, Salary) VALUES ('Amit Sharma', 'CEO', 'Management', NULL, '2010-01-01', 300000);  -- CTO & CFO INSERT INTO Employee VALUES ('Rahul Verma', 'CTO', 'Technology', 1, '2012-03-15', 200000), ('Neha Singh', 'CFO', 'Finance', 1, '2013-06-20', 190000);  -- Tech Managers INSERT INTO Employee VALUES ('Suresh Kumar', 'Dev Manager', 'Technology', 2, '2015-02-10', 150000), ('Pooja Mehta', 'QA Manager', 'Technology', 2, '2016-05-05', 140000);  -- Developers & QA INSERT INTO Employee VALUES ('Ankit Patel', 'Senior Developer', 'Technology', 4, '2018-07-01', 100000), ('Ravi Mishra', 'Junior Developer', 'Technology', 4, '2019-09-15', 80000), ('Kiran Joshi', 'QA Engineer', 'Technology', 5, '2020-01-20', 70000); -- Finance Team INSERT INTO Employee VALUES ('Sunita Rao', 'Accountant', 'Finance', 3, '2017-11-11', 90000);

 See the data into the table

To achieve this, we are creating the recursive CTE.

Read more about CTE

https://bageshkumarbagi-msbi.blogspot.com/2016/05/cte-common-table-expressions-in-sql.html

https://bageshkumarbagi-msbi.blogspot.com/2019/08/recursive-cte-error-maximum-recursion.html

below script is used to get the employee subordinates

WITH OrgCTE AS (     -- Anchor (manager)     SELECT         EmpID,         EmpName,         ManagerID,                              JobTitle,         0 AS Level,         CAST(EmpName AS VARCHAR(MAX)) AS HierarchyPath     FROM Employee     WHERE ManagerID is null   -- Manager A      UNION ALL      -- Recursive     SELECT         e.EmpID,         e.EmpName,         e.ManagerID,                              e.JobTitle,         c.Level + 1,         CAST(c.HierarchyPath + ' > ' + e.EmpName AS VARCHAR(MAX))     FROM Employee e     JOIN OrgCTE c         ON e.ManagerID = c.EmpID ) SELECT * FROM OrgCTE ORDER BY Level;

See the result below.

Using a self-referencing table with EmpID and ManagerID, and querying hierarchy using recursive CTE.