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>


Reply via email to