On 3/20/06, Ian M. Jones <[EMAIL PROTECTED]> wrote: > > 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. >
You understand it right, but since your where clause was on XC not on XCAT those results where dropped by the where clause.
