Re: [sqlite] "where not exists (union-select)" fails on 2nd where
On 24.08.2005, at 03:21, Kurt Welgehausen wrote: SELECT * FROM PRIM AS P WHERE NOT EXISTS ( SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID UNION SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID ); I looks like correct SQL according to the SQLite docs, but I don't understand why you coded the select that way. You should get the same result from select * from prim as p where not exists (select refid from ref1 where refid=p.id) and not exists (select refid from ref2 where refid=p.id) I would guess that this form would be more efficient because if the first test fails, the second sub-select should not be executed, perhaps saving a complete scan of ref2. perfect - thanks a lot! -Markus --- Markus W. Weissmann http://www.mweissmann.de/ http://www.opendarwin.org/~mww/
Re: [sqlite] "where not exists (union-select)" fails on 2nd where
> SELECT * FROM PRIM AS P > WHERE NOT EXISTS > ( > SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID > UNION > SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID > ); I looks like correct SQL according to the SQLite docs, but I don't understand why you coded the select that way. You should get the same result from select * from prim as p where not exists (select refid from ref1 where refid=p.id) and not exists (select refid from ref2 where refid=p.id) I would guess that this form would be more efficient because if the first test fails, the second sub-select should not be executed, perhaps saving a complete scan of ref2. Regards
[sqlite] "where not exists (union-select)" fails on 2nd where
Hi folks, I've got a little problem with a - at least I think so - correct SQL- statement: three tables, two referencing the 1st one --- SELECT * FROM PRIM AS P WHERE NOT EXISTS ( SELECT REFID FROM REF1 WHERE REF1.REFID=P.ID UNION SELECT REFID FROM REF2 WHERE REF2.REFID=P.ID ); --- chokes with "SQL error: no such column: P.ID"; as long as I do not use a WHERE statement in the 2nd 'inner' SELECT statement, it works as expected. I've worked around this by creating a VIEW from the union of the two referencing tables so I do not need the UNION in the original query. Is this a bug or did I just not get the docs? thanks, -Markus PS: sqlite really rocks & the C-API doc is very nice! --- Markus W. Weissmann http://www.mweissmann.de/ http://www.opendarwin.org/~mww/