Percorrendo uma hierarquia

Parte da seção Além do Básico do Journey de SQL da Coddy. Lição 21 de 27.

O verdadeiro motivo da existência das CTEs recursivas: percorrer uma relação pai/filho armazenada em uma única tabela. Suponha que employees tenha uma coluna manager_id que aponta de volta para a mesma tabela:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- âncora: o funcionário inicial
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- recursivo: pular para o gerente
)
SELECT * FROM chain

Cada etapa faz a junção da CTE novamente com employees, subindo um nível na árvore. A recursão para naturalmente quando uma row não tem manager (JOIN não encontra correspondências).

Desafio

Médio

Tabelas e colunas disponíveis:
categories: id, name, parent_id

Cada categoria tem um parent_id opcional que aponta para outra categoria. Retorne todas as categorias que são descendentes da categoria id = 1 (seus filhos, netos, …), mas não a própria categoria 1. Retorne id e name, ordenados por id.

Experimente você mesmo

WITH RECURSIVE descendants AS (
    -- âncora: filhos diretos do id 1
    -- recursivo: seus filhos, etc.
)
SELECT id, name FROM descendants ORDER BY id