SQL

parent child hierarchy in sql

WITH RECURSIVE generation AS (
    SELECT id,
        first_name,
        last_name,
        parent_id,
        0 AS generation_number
    FROM parent_child
    WHERE parent_id IS NULL
 
UNION ALL
 
    SELECT child.id,
        child.first_name,
        child.last_name,
        child.parent_id,
        generation_number+1 AS generation_number
    FROM parent_child child
    JOIN generation g
      ON g.id = child.parent_id
)
 
SELECT first_name,
     last_name,
     generation_number
FROM generation;