with recursive foo (X_id) as ( values (21) union select Aelements.X_id from X inner join Aelements on X.A_id = Aelements.object_id where X_id is not null ) select Aelements.* from X inner join Aelements on X.A_id = Aelements.object_id where X.id in foo and Aelements.B_id is not null;
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of MM Sent: Friday, June 15, 2018 2:03 PM To: SQLite mailing list; ryansmit...@gmail.com Subject: Re: [sqlite] Recursive query > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users