Recorriendo una jerarquía

Parte de la sección Más allá de lo básico del Journey de SQL de Coddy. Lección 21 de 27.

La verdadera razón por la que existen las CTE recursivas: recorrer una relación padre/hijo almacenada en una sola tabla. Supón que employees tiene una columna manager_id que apunta de nuevo a la misma tabla:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- ancla: el empleado inicial
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- recursivo: saltar al gerente
)
SELECT * FROM chain

Cada paso vuelve a unir el CTE con employees, subiendo un nivel en el árbol. La recursión se detiene de forma natural cuando una row no tiene manager (JOIN no encuentra ninguna coincidencia).

Desafío

Intermedio

Tablas y columnas disponibles:
categories: id, name, parent_id

Cada categoría tiene un parent_id opcional que apunta a otra categoría. Devuelve todas las categorías que son descendants de la categoría id = 1 (sus children, nietos, …), pero no la categoría 1 en sí. Devuelve id y name, ordenados por id.

Pruébalo tú mismo

WITH RECURSIVE descendants AS (
    -- ancla: hijos directos del id 1
    -- recursivo: sus hijos, etc.
)
SELECT id, name FROM descendants ORDER BY id