recursive query herarchical data sql server 1

recursive query herarchical data sql server

WITH cte_org AS (
    SELECT       
        staff_id, 
        first_name,
        manager_id
        
    FROM       
        sales.staffs
    WHERE manager_id IS NULL
    UNION ALL
    SELECT 
        e.staff_id, 
        e.first_name,
        e.manager_id
    FROM 
        sales.staffs e
        INNER JOIN cte_org o 
            ON o.staff_id = e.manager_id
)
SELECT * FROM cte_org;
Code language: SQL (Structured Query Language) (sql)

Here is what the above code is Doing:
1. The first SELECT statement in the CTE returns all the employees who are managers.
2. The second SELECT statement in the CTE returns all the employees who are not managers.
3. The second SELECT statement in the CTE is joined to the first SELECT statement in the CTE.
4. The final SELECT statement returns all the employees in the CTE.

Similar Posts