Hope this is the proper forum for this question:
I have a sqlite3 database that i am trying to query to return specific records. One query is returning 137 (minus the matches i enter in the NOT IN) rows which should be right, the other 16k+ which is wrong,, many,many duplicates. I'm sure it's how i'm constructing the query, or may just be undoable. The 2 tables pid and pv1 will always have the same row count in them and the orc varies but is the join. Any help would be appreciated. I've posted the table creates and the queries with their count().
Code:
sqlite>.schema pid
CREATE TABLE pid(msgid integer, field1 varchar, field2 varchar, field3 varchar, field4 varchar, field5 varchar, field6 varchar, field7 varchar, field8 varchar, field9 varchar, field10 varchar, field11 varchar, field12 varchar, field13 varchar, field14 varchar, field15 varchar, field16 varchar, field17 varchar, field18 varchar, field19 varchar, field20 varchar);
sqlite> .schema pv1
CREATE TABLE pv1(msgid integer, field1 varchar, field2 varchar, field3 varchar, field4 varchar, field5 varchar, field6 varchar, field7 varchar, field8 varchar);
sqlite> .schema orc
CREATE TABLE orc(msgid integer, field1 varchar, field2 varchar, field3 varchar, field4 varchar, field5 varchar, field6 varchar, field7 varchar, field8 varchar, field9 varchar, field10 varchar, field11 varchar, field12 varchar, field13 varchar, field14 varchar);
sqlite> SELECT count(), pid.msgid, pid.field4, orc.msgid, orc.field5 FROM pid JOIN pv1, orc ON pid.msgid = orc.msgid AND pv1.field4 NOT IN ('GLEH') ORDER BY pid.field4, orc.field5;
16440|137|xxxxxx|137|681602494
sqlite> SELECT count(), pid.msgid, pid.field4, orc.msgid, orc.field5 FROM pid JOIN orc ON pid.msgid = orc.msgid ORDER BY pid.field4, orc.field5;
137|137|xxxxxx|137|681602494
dBforums
>
SQLite query
01-22-06, 17:41
Linear Mode
