SQL Lab — Recursive CTEs

🖥️

SQL Terminal

🌳

Org Chart

📚

CTE Reference

📌 OBJECTIVES

○Write anchor query (Level 1)

○Add recursive member

○Include depth level column

○Build indented path column

○Filter to specific subtree

○Find all reports under a manager

0 / 6 complete

SQL TERMINAL — org_db

MySQL 8.0 — org_db Table: employees (id, name, title, manager_id, department) Task: Use WITH RECURSIVE to traverse the org chart hierarchy Standard GROUP BY queries cannot handle self-referential trees — use recursive CTEs ──────────────────────────────────────

mysql>

ORG CHART — visual hierarchy

Run a recursive CTE query to visualise the org chart here.

RECURSIVE CTE REFERENCE

BASIC STRUCTURE

WITH RECURSIVE cte_name AS ( -- Anchor: starting point SELECT id, name, manager_id, 0 AS depth, name AS path FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive: join back to CTE SELECT e.id, e.name, e.manager_id, c.depth + 1, CONCAT(c.path, ' > ', e.name) FROM employees e JOIN cte_name c ON e.manager_id = c.id ) SELECT * FROM cte_name ORDER BY depth, path;

FILTER TO SUBTREE

-- Start from a specific manager: WHERE manager_id = 3 -- in anchor -- Add depth limit (safety): WHERE depth < 10

KEY RULES

• Anchor + UNION ALL + recursive member
• Recursive part references the CTE by name
• Always add a depth limit to avoid infinite loops
• CONCAT builds the full path string

MISSION BRIEFING

RECURSIVE CTEs — MERIDIAN GROUP HR

SCENARIO

Meridian Group's HR team needs to answer questions that require walking the entire management hierarchy: who reports (directly or indirectly) to a given manager, how deep each person sits in the org, and what their full reporting path looks like. A standard SQL query cannot do this. You need a recursive CTE — a query that calls itself repeatedly until no more rows are returned.

THE TABLE

employees(id, name, title, manager_id, department)

Rows: 1 CEO → 3 VPs → 9 Directors → 24 Managers → 48 ICs = 85 total. manager_id is NULL for the CEO (root node).

TASK SEQUENCE

Start with the anchor: WHERE manager_id IS NULL — this returns just the CEO
Add the recursive member: JOIN org_tree ON e.manager_id = org_tree.id
Add depth + 1 to track hierarchy level
Add CONCAT(path, ' > ', e.name) to build full reporting path
Filter to Sarah Chen's subtree (id = 3) using anchor: WHERE id = 3
Find all direct and indirect reports under Marcus Reid (id = 5)

TIP

The Reference window has a complete working template. Type or paste the full WITH RECURSIVE block — the terminal recognises the key clauses. The Org Chart window visualises the result after each successful run.

Display Mode