I have the following table: ( I am using sqlite)
.schema T
CREATE TABLE T (i TEXT, j TEXT, v INTEGER);
select * from T;
i |j |v
x |y |1
x |z |1
y |a |1
z |b |1
a |c |1
c |d |1
I need to find the closure of a particular i to a given depth. However, with SQLite I can't use recursive queries. I tried writing one query but can't invoke it efficiently to get the desired result:
sqlite> Select i,j,v FROM T WHERE i='x'
...> union
...> SELECT R.i "i",T.j "j",R.v+t.v "v" FROM T JOIN T as R ON R.j=T.i where R.i='x';
i|j|v
x|a|2
x|b|2
x|y|1
x|z|1
This lists all the closures to a depth of 2. But in my particular use case I need to allow for closures up to a depth of 8.
With this types of query I can try:
SELECT i,j,v FROM T WHERE i='x'
union
SELECT R.i "i",T.j "j",R.R.x+T.v "x" FROM T JOIN (SELECT R.i,T.j,R.v+T.v "x" FROM T JOIN T AS
R ON R.j=T.i where R.i='x') AS R ON R.j=T.i where R.i='x'
union
SELECT R.i "i",T.j "j",R.v+T.v "x" FROM T JOIN T AS R ON R.j=T.i where R.i='x';
i|j|v
x|a|2
x|b|2
x|c|3
x|y|1
x|z|1
This will return closures to a depth of 3, but with redundant select queries. Is there a better way to find the closures?
