>
> 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
Hi Ryan,
I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.
Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back to the
original problem and failed miserably :-(
The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.
So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.
Table X
id A_id
=========
21 1
23 3
....
Table A
A_id
======
1
3
...
Table Aelements
object_id index X_id B_id
=========================
1 0 NULL 5
1 1 23 NULL
1 2 NULL 7
3 0 NULL 2
3 1 NULL 3
...
Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:
WITH RECURSIVE AE(object_id, X_id, B_id) AS (
SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
UNION ALL
SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;
But I think I'm failing to express the recursion in the above.
It doesn't work.
Basically in above, the resulting flattened Aelements list should be 1 3,
and therefore the corresponding B_id should be 5 7 2 3 , and finally the
last step from table B.
Rds,
MM
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
