SQL Common Table Expression (CTE) example

sql-cte.sql

WITH EmployeeList
(employee_id, employee_name, manager_id, manager_name, employee_level)
	AS (
			SELECT
				Boss.employee_id,
				Boss.employee_name,
				Boss.manager_id,
				Boss.manager_name,
				1 AS employee_level
			FROM EmployeeRoster Boss
			WHERE
				Boss.employee_id = 'topLevelIDHere'
		
		UNION ALL
		
			SELECT
				Emp.employee_id,
				Emp.employee_name,
				Emp.manager_id,
				Emp.manager_name,
				El.EmpLevel + 1 AS employee_level
			FROM EmployeeRoster Emp 
			INNER JOIN EmployeeList El ON 
				Emp.manger_id = El.employee_id
			WHERE
				Emp.employee_id <> 'topLevelIDHere'
	)


SELECT * FROM EmployeeList