Using Common Table Expressions to recursively grab employee reporting structures

CTE.sql

-- Traces up from leaf to root
WITH org(id, name, title, department, manager) AS (
    SELECT
        id, name, title, department, manager
    FROM
        employee
    WHERE id = '9E033E7A-9D49-445F-BB8E-C6B777DFC19E'
    UNION ALL
    SELECT
        e.id, e.name, e.title, e.department, e.manager
    FROM
        employee e INNER JOIN org o on e.id = o.manager
)
SELECT * FROM org;

-- Traces from root to leaves
WITH org(id, name, title, department, manager) AS (
    SELECT
        id, name, title, department, manager
    FROM
        employee
    WHERE id = '2CDE23A1-13A2-4D76-9004-009F7E8109B3'
    UNION ALL
    SELECT
        e.id, e.name, e.title, e.department, e.manager
    FROM
        employee e INNER JOIN org o on e.manager = o.id
)
SELECT * FROM org;