sqlite - SQL select descendants of a row -

suppose tree structure implemented in sql this:

create table nodes (     id integer primary key,     parent integer -- references nodes(id) ); 

although cycles can created in representation, let's assume never let happen. table store collection of roots (records parent null) , descendants.

the goal to, given id of node on table, find nodes descendants of it.

a descendant of b if either a's parent b or a's parent descendant of b. note recursive definition.

here sample data:

insert nodes values (1, null); insert nodes values (2, 1); insert nodes values (3, 2); insert nodes values (4, 3); insert nodes values (5, 3); insert nodes values (6, 2); 

which represents:

1 `-- 2     |-- 3     |   |-- 4     |   `-- 5     |     `-- 6 

we can select (immediate) children of 1 doing this:

select a.* nodes parent=1; 

we can select children , grandchildren of 1 doing this:

select a.* nodes parent=1 union select b.* nodes a, nodes b a.parent=1 , b.parent=a.id; 

we can select children, grandchildren, , great grandchildren of 1 doing this:

select a.* nodes parent=1 union select b.* nodes a, nodes b a.parent=1 , b.parent=a.id union select c.* nodes a, nodes b, nodes c a.parent=1 , b.parent=a.id , c.parent=b.id; 

how can query constructed gets descendants of node 1 rather @ fixed depth? seems need create recursive query or something.

i'd know if such query possible using sqlite. however, if type of query requires features not available in sqlite, i'm curious know if can done in other sql databases.

some databases allow using recursive common table expressions, not sqllite.

you consider changing table definition. table this, it's easy query descendants of 1:

id (varchar) -------------- 001 001002 001002003 001002003004 001002003005 001002006 

this allows query descendants of 1 like:

select * yourtable id '001%' 

it sounds bit whacky, works in practice.