Direct Manager and Skip-Level Manager
For each employee show both the direct manager and the skip-level manager (manager's manager). Use two LEFT JOINs so employees without managers (or without skip-level managers) still appear with NULLs. Return employee_id, employee_name, direct_manager_name, skip_level_manager_name — ordered by employee_id.
- Joins
- Sorting
Exercise brief
Understand the request
Succession planning lead A leadership pipeline report needs both the direct and skip-level manager while retaining incomplete chains.
Return
- Return employee identity plus direct and skip-level manager names.
- Order by employee_id.
Constraints
- Join employees to itself twice.
- Use LEFT JOIN for both hierarchy levels.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERemployee_nameTEXTmanager_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Chain two self-joins: first to direct manager, then to direct-manager's manager.
Hint 2
Keep both as LEFT JOINs — the CEO has no manager, VPs have no skip-level manager.
Hint 3
This is the static-depth alternative to a recursive CTE (Q6) when you know the depth in advance.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.employee_name, m1.employee_name AS direct_manager_name, m2.employee_name AS skip_level_manager_name FROM employees e LEFT JOIN employees m1 ON e.manager_id = m1.employee_id LEFT JOIN employees m2 ON m1.manager_id = m2.employee_id ORDER BY e.employee_id;Why this works
For fixed-depth lookups, repeated self-joins are simpler and faster than recursive CTEs. They are also the only option on engines without recursive-CTE support (e.g., MySQL 5.7).
Success check
Every employee appears once with NULLs wherever the management chain ends.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | employee_name | direct_manager_name | skip_level_manager_name |
|---|---|---|---|
| 1 | Alice CEO | NULL | NULL |
| 2 | Bob VP Sales | Alice CEO | NULL |
| 3 | Carol VP Eng | Alice CEO | NULL |
| 4 | David VP HR | Alice CEO | NULL |
| 5 | Emma Sales Mgr | Bob VP Sales | Alice CEO |
| 6 | Frank Eng Mgr | Carol VP Eng | Alice CEO |
| 7 | Grace HR Mgr | David VP HR | Alice CEO |
| 8 | Henry Sales Rep | Emma Sales Mgr | Bob VP Sales |
| 9 | Ivy Sales Rep | Emma Sales Mgr | Bob VP Sales |
| 10 | Jack Engineer | Frank Eng Mgr | Carol VP Eng |
Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Open the interactive workspace and practice across SQL topics.