*I’m now wondering if you omit the WHERE & ORDER BY and run the following
EXPLAIN QUERY PLAN SELECT BaseTbl.RowID FROM BaseTbl left join Tbl1 on comparison_1 left join Tbl2 on comparison_2 . . left join Tbln on comparison_n then if it returns more than 1 row then this implies there’s a 1 to many relationship in the query and we can’t proceed. (At this stage I’m by no means sure of this). * The above isn't guaranteed to work. Consider the following CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER); CREATE INDEX ib ON t1(b); EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON x = b; 0|0|0|SCAN TABLE t1 Everything above is as expected, t2.x is primary key lookup for t1.b so trailing left join is dropped. Suppose though we made t2.y the lookup for t1.b CREATE UNIQUE INDEX iy ON t2(y); EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON y = b; 0|0|0|SCAN TABLE t1 0|1|1|SEARCH TABLE t2 USING COVERING INDEX iy (y=?) As you can see what at first sight looks like a redundant trailing left join ISN’T dropped. If however we had defined t2 with CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER NOT NULL); Then we would get EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON y = b; 0|0|0|SCAN TABLE t1 Therefore, the suggested formula would fail on any non premium key lookup index where the index field(s) were not declared as NOT NULL. Strangely (or maybe not) EXPLAIN QUERY PLAN SELECT t1.RowID FROM t1 LEFT JOIN t2 ON y = b *WHERE y IS NOT NULL*; doesn't resolve the problem. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
