Recursive CTE as correlated subquery?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Recursive CTE as correlated subquery?

Randall Smith
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Recursive CTE as correlated subquery?

Keith Medcalf

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 <[hidden email]> On Behalf
>Of Randall Smith
>Sent: Tuesday, 10 September, 2019 18:45
>To: [hidden email]
>Cc: Randall Smith <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users