[Solved] You have a departments table with columns id, name, and parent_id. Write a query to list each department with its top-level ancestor department name (the highest parent in the hierarchy).... | SQL

SQL - Advanced Joins

You have a departments table with columns id, name, and parent_id. Write a query to list each department with its top-level ancestor department name (the highest parent in the hierarchy). Which approach is correct?

AUse UNION ALL to combine all departments without joins

BUse recursive CTE to repeatedly join the table to itself until the top parent is found

CUse GROUP BY on parent_id to find the top parent

DUse a simple self join once on parent_id = id to get the top parent

Step-by-Step Solution

Solution:

Step 1: Understand hierarchy depth
Top-level ancestor may be multiple levels above, so one join is not enough.
Step 2: Use recursive CTE
Recursive CTE allows repeated self joins until the highest parent (where parent_id is NULL) is found.
Final Answer:
Use recursive CTE to repeatedly join the table to itself until the top parent is found -> Option B
Quick Check:
Recursive CTE needed for multi-level hierarchy [OK]

Quick Trick: Use recursive CTE for multi-level parent lookup [OK]

Common Mistakes:

MISTAKES

Trying single self join for multi-level hierarchy
Using GROUP BY incorrectly
Ignoring recursion for ancestors

Master "Advanced Joins" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Which approach is correct?

Step 1: Understand hierarchy depth

Step 2: Use recursive CTE

Final Answer:

Quick Check:

Want More Practice?