On Sep 1, 2009, at 12:46 AM, Doug wrote:
> I have two simple tables - one that defines a statistic, and one
> that hold
> the data for each statistic:
>
> CREATE TABLE Statistic
>
> (
>
> StatID INTEGER PRIMARY KEY,
>
> OwningComputer TEXT NOT NULL
>
> );
>
>
>
> CREATE TABLE StatData
>
> (
>
> StatID INTEGER NOT NULL,
>
> Value INTEGER NOT NULL,
>
> Date INTEGER NOT NULL
>
> );
>
>
>
> and indices.
>
> CREATE INDEX Ind_StatData_StatID on StatData (StatID, Date);
>
> CREATE INDEX Ind_StatData_Date on StatData (Date);
>
>
>
> I'm trying to figure out the best way to find any entries in
> Statistic that
> don't have any corresponding entries in StatData (ie what Statistic is
> defined, but no longer has any data attached).
>
>
>
> The following query works correctly, but when there is a few 100MB of
> StatData entries, it's pretty slow:
>
>
>
> SELECT StatID FROM Statistic WHERE StatID NOT IN (SELECT StatID FROM
> StatData GROUP BY StatID);
Actually, now that I think about it, you'll have to use a unary + to
trick SQLite as StatID is an INTEGER PRIMARY KEY... Maybe experiment
with things like this:
SELECT StatID FROM Statistic WHERE +StatID NOT IN (
SELECT StatID FROM StatData
);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
