Answers for "using sql query to find all descendants of a row"

using sql query to find all descendants of a row

WITH RECURSIVE descendant AS (
    SELECT  id,
            first_name,
            last_name,
            parent_id,
            0 AS level
    FROM family_tree
    WHERE id = 1
 
    UNION ALL
 
    SELECT  ft.id,
            ft.first_name,
            ft.last_name,
            ft.parent_id,
            level + 1
    FROM family_tree ft
JOIN descendant d
ON ft.parent_id = d.id
)
 
SELECT  d.id AS descendant_id,
        d.first_name AS descendant_first_name,
        d.last_name AS descendant_last_name,
        a.id AS ancestor_id,
        a.first_name AS ancestor_first_name,
        a.last_name AS ancestor_last_name,
        d.level
FROM descendant d
JOIN family_tree a
ON d.parent_id = a.id
ORDER BY level, ancestor_id;