Hierarchical Join — Solution & ExplanationProblems / Hierarchical Join / EditorialApproach

LEFT JOIN categories c to categories p on c.parent_id = p.category_id.

This maps each category to its parent's row, giving access to the parent's name.

Root categories have NULL parent_id, so the LEFT JOIN produces NULL for parent_name.

Key InsightHierarchical data stored in an adjacency list (parent_id references the same table) is resolved with self-joins. Each join resolves one level. For deeper hierarchies (grandparent, etc.), you'd chain additional self-joins or use recursive CTEs.Hints

This is a self-join — the same table is used as both child and parent.

Use LEFT JOIN so root categories (NULL parent_id) still appear.

Alias the table as 'c' for child and 'p' for parent to keep it clear.

Solve Hierarchical Join yourself →