Walking a Hierarchy

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 21 of 27.

The real reason recursive CTEs exist: walking a parent/child relationship stored in a single table. Suppose employees has a manager_id column that points back into the same table:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- anchor: the starting employee
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- recursive: jump to the manager
)
SELECT * FROM chain

Each step joins the CTE back to employees, hopping one level up the tree. The recursion stops naturally when a row has no manager (JOIN matches nothing).

Challenge

Medium

Available tables and columns:
categories: id, name, parent_id

Each category has an optional parent_id pointing at another category. Return all categories that are descendants of category id = 1 (its children, grandchildren, …) but not category 1 itself. Return id and name, ordered by id.

Cheat sheet

Use recursive CTEs to walk parent/child relationships stored in a single table (e.g., a manager_id or parent_id column pointing back to the same table):

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7          -- anchor: starting row
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id  -- recursive: hop to parent
)
SELECT * FROM chain

Recursion stops naturally when the JOIN finds no matching rows (e.g., a row with no manager).

Try it yourself

WITH RECURSIVE descendants AS (
    -- anchor: direct children of id 1
    -- recursive: their children, etc.
)
SELECT id, name FROM descendants ORDER BY id