You can do it with a JOIN instead of IN and I'm pretty sure it will still
use an index.
SELECT
COUNT(*)
FROM
guid_version_map M
LEFT JOIN
latest_version V
ON
M.guid = V.guid AND M.version = V.version
WHERE
V.guid IS NULL;
HTH,
Sam
-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-----Original Message-----
From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED]
Sent: Friday, June 29, 2007 5:35 PM
To: [email protected]
Subject: [sqlite] Multi-column "in" clause supported?
I'm attempting to execute this SQL statement ( using SQLiteSpy, if that
matters, which is based on 3.3.16 ):
select count(*) from guid_version_map
where (guid,version) not in (select guid, version from latest_version)
Basically I want to retrieve the rows in guid_version_map whose primary key
(guid,version) are not present in some other table. The error reported is
a "syntax error near ",", which I assume is where the portion of the "where
(guid,version)" tuple gets defined.
Does this imply that SQLite does not support the multi-column "in" clause?
I can code this up in a procedural way, but I love making the database
execute as much stuff as possible, reducing the amount of inter-database
interface to my C++ code as possible.
--andy
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
