Results 1 to 2 of 2

Thread: SQLite query

  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: SQLite query

    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
    Last edited by dcoyaz; 01-22-06 at 18:48.

  2. #2
    Join Date
    Mar 2004
    Posts
    2
    Ok, sometimes i just don't think to well on Sundays but got it figured out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •