Is it possible to write a SQLite query which recursively gets all items that are child items of a root node
sql, sqlite
Solution
I just got a similar query to work using the `with recursive` syntax. The general form is:
with recursive tc( i )
as ( select [... initial-query ...]
union [... recursive-part (include tc) ...]
)
select * from tc;
The key in my case was to make sure that tc was listed in the recursive part. Also, this final select is just to show the full content of the transitive closure, the real one should select the rows that you need.
I think that this recipe would apply to your case as the following. I haven't tested this, I'm just copy/pasting from my query and replacing with your table names. It does work for me, but I might have translated this incorrectly. I'm also not really sure about efficiency, etc., it is just something that I got to work.
with recursive tc( i )
as ( select childItemID from itemItems where parentItemID = 1
union select childItemID from itemItems, tc
where itemItems.parentItemID = tc.i
)
select * from item where itemID in tc;
NOTE: This worked for me on version 3.8.3.1 but not on 3.7.2.
Problem
I have 2 tables. `items` and `itemItems` `itemItems` describes a many to many relationship between `items`. I.e. a member of `items` could have many children and they could have many children which in turn could have many children etc.. item: ``` itemID | more stuff ...... 1 ... 2 ... 3 ... 4 ... ``` itemItems: ``` parentItemID | childItemID 1 2 1 3 2 4 ``` I want to write a query that would recursively get all of the children under one root node. I believe this is possible with something called a recursive join but I find the concept very confusing.... (similar to this question, but with sqlite not sql server and many to many not one to many) I can get the first level (i.e. all children under one item) by doing the following ``` SELECT * FROM items INNER JOIN itemItems ON items.itemID = itemItems.childItemID WHERE itemItems.parentItemID = 1 ``` How could I extend this to recursively get all the children's children etc...?