sql- Part 2 SQL query to get organization hierarchy

Part 2 SQL query to get organization hierarchy

1.Self-Join
2.CTE
3.Recursive CTE

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 EmployeeCTE

EmployeeID   EmployeeName  ManagerID

7                      David                  4

4                      Tom                     3

3                       Steve                  8

8                       Ben                     NULL

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,E2.EmployeeName as ManagerName 

from EmployeeCTE E1

JOIN EmployeeCTE E2

ON E1.MANAGERID =E2.EmployeeID

EmployeeName   ManagerName

David                    Tom

Tom                       Steve

Steve                      Ben

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,E2.EmployeeName as ManagerName 

from EmployeeCTE E1

Left JOIN EmployeeCTE E2

ON E1.MANAGERID =E2.EmployeeID

EmployeeName   ManagerName

David                    Tom

Tom                       Steve

Steve                      Ben

Ben                         NULL

Introduction to Hierarchical Query - SQL in Sixty Seconds #012

Recursive CTE work.

WITH CTE AS
(
SELECT BOSS.EmpID,BOSS.LastName
FROM Employee as BOSS
WHERE BOSS.ManagerID IS NULL

--ANCHOR query runs first
--EmpID  LastName  ManagerID  lvl
--11          Smith         NULL          1

UNION ALL

SELECT  Emp.EmpID,Emp.LastName,Emp.ManagerID,CTE.lvl+1
FROM Employee as Emp INNER JOIN CTE
ON Emp.ManagerID=CTE.EmpID
WHERE Emp.ManagerID IS NOT NULL)

SELECT * FROM CTE