Something like this? Your seed number will go into the values statement at the
start.
with recursive foo (A_id) as (
values (1)
union
select A.A_id
from
foo inner join A
on foo.A_id = A.object_id
where A.A_id is not null
)
select distinct B.data
from
foo inner join A
on foo.A_id = A.object_id
inner join B
on A.B_id = B.object_id;
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of MM
Sent: Friday, June 15, 2018 9:19 AM
To: SQLite mailing list
Subject: [sqlite] Recursive query
I have table A of rows with column "object_id", and 2 NULL columns
A_id and B_id.
Each row of A (among rows with the same object_id) either references
table A itself or table B, ie columns A_id and B_id are mutually
exclusive.
e.g.
Table A
object_id A_id B_id
...
1 NULL 5
1 3 NULL
1 NULL 7
3 NULL 2
3 NULL 3
...
Table B
object_id data
...
2 15
3 16
5 17
7 18
...
My objective is to collect all unique "data" in all the B rows that
belong to a given A object_id (like '1') and all its "descendants".
For e.g.:
given object "1", the result of the query would be the list 17, 18.
15, 16 in no particular order
given object "3", the result of the query would be the list 15 16
Can this be done with a recursive cet query? If so, how?
Rds,
_______________________________________________
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
