Dennis, Thank you for researching this. I did a similar simple test and it worked fine. I finally figured out the issue. The PriceLevelPerItem.ListID is not unique and the user has several values for each PriceLevelPerItem.ListID in his database. Back to Database Basics 101 for me.
Thanks again for your time, Matt Froncek QODBC Development Support / FLEXquarters Solutions Consultant QODBC Driver for QuickBooks - Unleash your data at www.qodbc.com -----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 2:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Left Outer Join Matt Froncek wrote: > >There must be a bug then. Because my TimeTracking Table has 36,000 records >and that is what I get with the First LEFT OUTER JOIN and even with the >second LEFT OUTER JOIN but adding the third one I get 2,000,000 records. The >product of 36,000 and the records duplicated from PriceLevelPerItem). > > Matt, I can't reproduce your problem here. I have created some minimal tables that I believe match the relevant parts of your schema. When I run the query I get the correct result. I have included a trace of my test run below. Note, I'm using a different version of sqlite than you. What do you get when you when you run the following command in your version? create table TimeTracking ( ListID integer primary key, CustomerRefListID integer references customer, EntityRefListID integer references entity, t_data text ); insert into TimeTracking values (1, 1, 1, 't_one'); insert into TimeTracking values (2, 1, 2, 't_two'); create table Customer ( ListID integer primary key, PriceLevelRefListID intteger references pricelevelperitem, c_data text ); insert into Customer values (1, 1, 'c_one'); insert into Customer values (2, 1, 'c_two'); create table Entity ( ListID integer primary key, e_data text ); insert into Entity values (1, 'e_one'); insert into Entity values (2, 'e_two'); create table PriceLevelPerItem ( ListID integer primary key, p_data text ); insert into PriceLevelPerItem values (1, 'p_one'); insert into PriceLevelPerItem values (2, 'p_two'); explain query plan SELECT count(TimeTracking.ROWID) FROM TimeTracking LEFT OUTER JOIN Customer ON Customer.ListID = TimeTracking.CustomerRefListID LEFT OUTER JOIN Entity ON Entity.ListID = TimeTracking.EntityRefListID LEFT OUTER JOIN PriceLevelPerItem ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID; .explain on explain SELECT count(TimeTracking.ROWID) FROM TimeTracking LEFT OUTER JOIN Customer ON Customer.ListID = TimeTracking.CustomerRefListID LEFT OUTER JOIN Entity ON Entity.ListID = TimeTracking.EntityRefListID LEFT OUTER JOIN PriceLevelPerItem ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID; SELECT count(TimeTracking.ROWID) FROM TimeTracking LEFT OUTER JOIN Customer ON Customer.ListID = TimeTracking.CustomerRefListID LEFT OUTER JOIN Entity ON Entity.ListID = TimeTracking.EntityRefListID LEFT OUTER JOIN PriceLevelPerItem ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID; *************** Test Run below SQLite version 3.3.5 Enter ".help" for instructions sqlite> create table TimeTracking ( ...> ListID integer primary key, ...> CustomerRefListID integer references customer, ...> EntityRefListID integer references entity, ...> t_data text ...> ); sqlite> sqlite> insert into TimeTracking values (1, 1, 1, 't_one'); sqlite> insert into TimeTracking values (2, 1, 2, 't_two'); sqlite> sqlite> create table Customer ( ...> ListID integer primary key, ...> PriceLevelRefListID intteger references pricelevelperitem, ...> c_data text ...> ); sqlite> sqlite> insert into Customer values (1, 1, 'c_one'); sqlite> insert into Customer values (2, 1, 'c_two'); sqlite> sqlite> create table Entity ( ...> ListID integer primary key, ...> e_data text ...> ); sqlite> sqlite> insert into Entity values (1, 'e_one'); sqlite> insert into Entity values (2, 'e_two'); sqlite> sqlite> create table PriceLevelPerItem ( ...> ListID integer primary key, ...> p_data text ...> ); sqlite> sqlite> insert into PriceLevelPerItem values (1, 'p_one'); sqlite> insert into PriceLevelPerItem values (2, 'p_two'); sqlite> sqlite> sqlite> explain query plan ...> SELECT count(TimeTracking.ROWID) ...> FROM TimeTracking ...> LEFT OUTER JOIN Customer ...> ON Customer.ListID = TimeTracking.CustomerRefListID ...> LEFT OUTER JOIN Entity ...> ON Entity.ListID = TimeTracking.EntityRefListID ...> LEFT OUTER JOIN PriceLevelPerItem ...> ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID; 0|0|TABLE TimeTracking 1|1|TABLE Customer USING PRIMARY KEY 2|2|TABLE Entity USING PRIMARY KEY 3|3|TABLE PriceLevelPerItem USING PRIMARY KEY sqlite> sqlite> .explain on sqlite> explain ...> SELECT count(TimeTracking.ROWID) ...> FROM TimeTracking ...> LEFT OUTER JOIN Customer ...> ON Customer.ListID = TimeTracking.CustomerRefListID ...> LEFT OUTER JOIN Entity ...> ON Entity.ListID = TimeTracking.EntityRefListID ...> LEFT OUTER JOIN PriceLevelPerItem ...> ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID; addr opcode p1 p2 p3 ---- -------------- ---------- ---------- --------------------------------- 0 MemNull 1 0 1 MemNull 0 0 2 Goto 0 51 3 Integer 0 0 4 OpenRead 0 2 5 SetNumColumns 0 3 6 Integer 0 0 7 OpenRead 1 3 8 SetNumColumns 1 2 9 Integer 0 0 10 OpenRead 2 4 11 SetNumColumns 2 0 12 Integer 0 0 13 OpenRead 3 5 14 SetNumColumns 3 0 15 Rewind 0 43 16 MemInt 0 2 17 Column 0 1 18 MustBeInt 1 39 19 NotExists 1 39 20 MemInt 1 2 21 MemInt 0 3 22 Column 0 2 23 MustBeInt 1 36 24 NotExists 2 36 25 MemInt 1 3 26 MemInt 0 4 27 Column 1 1 28 MustBeInt 1 33 29 NotExists 3 33 30 MemInt 1 4 31 Rowid 0 0 32 AggStep 0 1 count(1) 33 IfMemPos 4 36 34 NullRow 3 0 35 Goto 0 30 36 IfMemPos 3 39 37 NullRow 2 0 38 Goto 0 25 39 IfMemPos 2 42 40 NullRow 1 0 41 Goto 0 20 42 Next 0 16 43 Close 0 0 44 Close 1 0 45 Close 2 0 46 Close 3 0 47 AggFinal 0 1 count(1) 48 MemLoad 0 0 49 Callback 1 0 50 Halt 0 0 51 Transaction 0 0 52 VerifyCookie 0 4 53 Goto 0 3 54 Noop 0 0 sqlite> sqlite> SELECT count(TimeTracking.ROWID) ...> FROM TimeTracking ...> LEFT OUTER JOIN Customer ...> ON Customer.ListID = TimeTracking.CustomerRefListID ...> LEFT OUTER JOIN Entity ...> ON Entity.ListID = TimeTracking.EntityRefListID ...> LEFT OUTER JOIN PriceLevelPerItem ...> ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID; coun ---- 2 sqlite>
