TreeAndSQL.md

DDL

CREATE TABLE vertex (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id INTEGER
);

Data

INSERT INTO vertex (ID, PARENT_ID)
VALUES

(1,1),
(2,1),
(3,1),
(4,1),
(5,2),
(6,2),
(7,3),
(8,7),
(9,3),
(10,7);

Query(SQLite3)

with recursive cte as (
    select * from vertex where id = 3
    union all
    select vertex.* from vertex
    inner join cte on vertex.parent_id = cte.id 
)
select * from cte

id	parent_id
3	1
7	3
9	3
8	7
10	7