You mean like this:
create table parent
(
id integer primary key,
data text not null collate nocase unique,
child_id integer not null references child
);
create index parent_child_id on parent (child_id);
create table child
(
id integer primary key,
recurse_id integer references child,
data text not null collate nocase
);
create index child_recurse_id on child (recurse_id);
insert into child values ( 1, NULL, 'End of Child Chain 1');
insert into child values ( 5, 1, 'Nx2 of Child Chain 1');
insert into child values (14, 5, 'Nx1 of Child Chain 1');
insert into child values ( 3, 14, 'Top of Child Chain 1');
insert into parent values (1, 'Parent of Child Chain 1', 3);
insert into child values (16, NULL, 'End of Child Chain 2');
insert into child values ( 4, 16, 'Nx2 of Child Chain 2');
insert into child values ( 7, 4, 'Nx1 of Child Chain 2');
insert into child values ( 2, 7, 'Top of Child Chain 2');
insert into parent values (2, 'Parent of Child Chain 2', 2);
select *,
(with a as (
select *
from child
where id=parent.child_id
union all
select child.*
from a, child
where a.recurse_id=child.id
)
select data
from a
where a.data like 'Nx2%') as childdata
from parent;
SQLite version 3.30.0 2019-09-10 13:20:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode col
sqlite> .head on
sqlite> .read test.sql
id data child_id childdata
---------- ----------------------- ---------- --------------------
1 Parent of Child Chain 1 3 Nx2 of Child Chain 1
2 Parent of Child Chain 2 2 Nx2 of Child Chain 2
sqlite>
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf
>Of Randall Smith
>Sent: Tuesday, 10 September, 2019 18:45
>To: sqlite-users@mailinglists.sqlite.org
>Cc: Randall Smith <rsm...@qti.qualcomm.com>
>Subject: [sqlite] Recursive CTE as correlated subquery?
>
>I have an application for doing recursive data lookup as part of a larger
>query. For example, for each node in a tree, are there any children of the
>node that have some property?
>
>In non-recursive settings, one would use a conventional correlated subquery
>that references the node id from the outer query to look up things about
>the node in the subquery. But with recursive CTEs there does not seem to be
>any way, syntactically or otherwise, to "parameterize" the starting point
>for the recursive operation.
>
>Is this just something you can't do in SQLite or SQL in general? Or am I
>missing something?
>
>Thanks for any suggestions or expertise.
>
>Randall.
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
