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

[drh]

"Ian M. Jones" <[EMAIL PROTECTED]> wrote:
> 
> select xcat.Category, xc.CaseID as NumCases
> from Category As xcat
> left outer join Cases as xc on xcat.CategoryID = xc.CategoryID
> where xc.CaseID in (3145)
> ;
> 

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.

--
D. Richard Hipp   <[EMAIL PROTECTED]>