On 2018/06/15 3:19 PM, MM wrote:
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?
Yes it can be done.
First you need, for a given object_id in A, all the references from A
that will eventually point to B (i.e. a non-null B_id). That is, you
need all the A_id items that point back into A to resolve to the entire
list of object_id rows in A that refers to B_id (and not A_id).
Thereafter it's a simple thing of taking the A list with B links,
linking the B values, and getting the Unique entries.
Step 1: Expand all the object_id items in A which point to another
object_id in A: This can be done recursively:
(Assume the given object ID is set in: ?1)
WITH AE(o_id,A_id) AS (
SELECT A.object_id, A.A_id FROM A WHERE A.object_id = ?1
UNION ALL
SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
)
SELECT AE.o_id FROM AE;
This gives the fully expanded list of A_id's that are referenced by
either the given id (?1) or a linking id in A (A_id).
Now let's simply join all the A table items that is in the list
described by AE, then join to that the B items where those links exist,
and then get the distinct items from those:
WITH AE(o_id,A_id) AS (
SELECT A.object_id, A.A_id FROM A WHERE A.object_id = ?1
UNION ALL
SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
)
SELECT DISTINCT B.data
FROM AE
JOIN A ON A.object_id = AE.o_id
JOIN B ON B.object_id = A.B_id
;
I don't have a testbed handy, but this should work - if not, please post
again (perhaps with a little more example data) so we can fix it.
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
