Esplorare una gerarchia

Fa parte della sezione Oltre le basi del percorso SQL di Coddy. Lezione 21 di 27.

Il vero motivo per cui esistono le CTE ricorsive: percorrere una relazione padre/figlio memorizzata in un’unica tabella. Supponiamo che employees abbia una colonna manager_id che rimanda alla stessa tabella:

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id
    FROM employees WHERE id = 7              -- ancora: il dipendente di partenza
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain c ON e.id = c.manager_id      -- ricorsivo: passa al responsabile
)
SELECT * FROM chain

Ogni passaggio unisce nuovamente la CTE a employees, risalendo di un livello nell'albero. La ricorsione si interrompe naturalmente quando una riga non ha un responsabile (JOIN non trova corrispondenze).

Sfida

Medio

Tabelle e colonne disponibili:
categories: id, name, parent_id

Ogni categoria ha un parent_id facoltativo che punta a un'altra categoria. Restituisci tutte le categorie discendenti della categoria id = 1 (i suoi figli, nipoti, …), ma non la categoria 1 stessa. Restituisci id e name, ordinati per id.

Provalo tu

WITH RECURSIVE descendants AS (
    -- base: figli diretti di id 1
    -- ricorsivo: i loro figli, ecc.
)
SELECT id, name FROM descendants ORDER BY id