Re: [sqlite] [SOLVED] Select with left outer join - Am I going mad or just having a bad day?

[Ian M. Jones]

On 20 Mar 2006, at 16:06, [EMAIL PROTECTED] wrote:

Perhaps you mean something more like this:

   select xcat.Category, xc.CaseID as NumCases
   from Cateogry AS xcat
   left outer join Cases as xc on xcat.CategoryID = xc.CategoryID
   where xc.CaseID in (3145) OR xc.CaseID IS NULL;

Or perhaps this (which is really the same thing):

   select xcat.Category, xc.CaseID as NumCases
   from Cateogry AS xcat
   left outer join Cases as xc
   on xcat.CategoryID = xc.CategoryID
   and xc.CaseID in (3145);

In your original query, when the right table of the left join
is NULL, then the WHERE clause always fails, effectively
converting your LEFT JOIN into a CROSS JOIN.


From what I know, the whole idea of a left outer join is that the  
left hand table always returns a result, regardless of whether the  
join to the right hand table succeeds or not.

So, the NULL check in your first example is superfluous, the Category  
table should always be returning a result for each of it's rows,  
whether there is a NULL record in the Cases match or not.

But, hurrahh! Your second example works!

So, now I've re-written the query as such:

select xcat.Category, count(xc.CaseID) as NumCases
from Category As xcat
left join Cases as xc on xcat.CategoryID = xc.CategoryID
and xc.CaseID in (3145)
group by xcat.Category
order by xcat.Category
;

And I get:

Bug|0
Feature|0
Inquiry|1

Exactly what I wanted!

Thank you drh, very much appreciated.

--
Ian M. Jones
___________________________________
IMiJ Software
http://www.imijsoft.com
http://www.ianmjones.net (blog)