Cem Dayanik (Ibtech-Software Infrastructure) wrote: > CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress > INTEGER) > CREATE TABLE Instances (TypeId INTEGER, Address INTEGER) > CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count INT, > TotalSize INTEGER) > > What I need is, start with an address and add all reference addresses to the > list -with increasing level- unless it is already there. > > Not working with: > and refbyaddress not in (select address from allreferences) > > select * from ( > WITH RECURSIVE > allreferences(address, level) AS ( > values(435582892, 0) > union > SELECT refbyaddress, allreferences.level+1 FROM instancereferences, > allreferences > WHERE instancereferences.instanceaddress=allreferences.address and > allreferences.level < 1 and refbyaddress not in (select address from > allreferences) > ) > SELECT* FROM allreferences ) r, instances ins , types t where r.n = > ins.address and ins.typeid=t.id
The documentation says (http://www.sqlite.org/lang_with.html#recursivecte): | The table named on the left-hand side of the AS keyword must appear | exactly once in the FROM clause of the right-most SELECT statement of | the compound select, and nowhere else. If you omit the level, the UNION automatically prevents loops. Do you really need the level? It might be possible to compute it in SQL afterwards, but that is likely to be inefficient. It might be a better idea to run Dijkstra's algorithm in your code. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
