RE: [sqlite] Left Outer Join

[Matt Froncek]

Thanks Denis.

I am just using the Count at the moment to see how many records are
returned. I do intend to really get fields in the final version.

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). 

Any suggestions?

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 11:13 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Left Outer Join

Matt Froncek wrote:

>What is the proper way nest my Left Outer Joins. In another ODBC driver I
>can use:
>
> 
>
>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
>
>  
>
Matt,

The way to say this in SQLite is:

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

But why do you want to do this?

A left join produces an output record for each record from the left table.
The left table in the first join is the TimeTracking table. So you will get
an output record for each record in this table. These records will be
extended by data from the other tables where the conditions match, or by
null columns. The only thing you are doing is counting the rowids from the
leftmost table. 

Your query is equivalent to

SELECT count(TimeTracking.ROWID) 
FROM TimeTracking 

I suspect you really want something else.

HTH
Dennis Cote