Helix Systems' leadership team wants to trace each employee's management chain two levels up.
Write a query to return the employee's name, their direct manager's name, and their manager's manager's name for every employee who has both levels available.
Assumptions:
- The
employeestable contains every active and former employee at Helix Systems. - The query needs three roles from the same table: the employee, their manager, and their manager's manager.
- An employee whose manager has no manager (the manager is at the top of the hierarchy) does not have a grand-manager and is excluded from the result.
Output:
- One row per qualifying employee, with columns
employee_name,manager_name, andgrand_manager_name.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
e.name AS employee_name,
m.name AS manager_name,
gm.name AS grand_manager_name
FROM
employees e
JOIN employees m ON e.manager_id = m.id
JOIN employees gm ON m.manager_id = gm.idThe shape
Three levels up the management chain means three aliased instances of employees in a single query. Each new level is one more JOIN employees ... ON <previous>.manager_id = <new>.id, walking one step up the hierarchy at each join.
Clause by clause
SELECT e.name AS employee_name, m.name AS manager_name, gm.name AS grand_manager_namepullsnamefrom three aliases — the employee, the manager, and the manager's manager. Three roles, three columns.FROM employees ereadsemployeesin the employee role.JOIN employees m ON e.manager_id = m.idwalks one step up. For each employee, find the row whoseidmatches the employee'smanager_id. That row is the manager.JOIN employees gm ON m.manager_id = gm.idwalks one more step up. The condition keys offm.manager_id, note.manager_id. Each manager's own manager is the grand-manager. Anna Kim's row joins to Marcus Reid viam, and Marcus Reid's row joins to Sarah Chen viagm.
Why this and not ON e.manager_id = gm.id
The second join has to chain off the first one, not loop back to the employee. m.manager_id = gm.id walks up from the manager's row, which is what "manager's manager" means. e.manager_id = gm.id would re-link the employee to their direct manager a second time, producing a result where manager_name and grand_manager_name are always identical. The directionality is what makes the chain work — each join's ON clause references the alias immediately preceding it.
The trap
INNER JOINs in a chain compound exclusions silently. Each JOIN employees ... ON ...manager_id = ...id requires a non-NULL manager_id on the previous level. Employees who have a manager but whose manager is Sarah Chen (no manager_id herself) survive the first join but disappear at the second. The prompt acknowledges this — "an employee whose manager has no manager does not have a grand-manager and is excluded" — but the exclusion is silent in the query. The shape doesn't change, the row count just shrinks. When chaining self-joins for hierarchy traversal, every level deepens the exclusion of rows whose chain is shorter than the depth being asked for.
You practiced a three-level self-join chain on a hierarchical table. Each new level is one more aliased instance, with each new condition linking the previous level's manager_id to the new alias's primary key.
