Hi Ian, You nee to use a Full Outer Join. I don't know if SQLite has this function.
At 03:23 PM 3/20/06 +0000, Ian M. Jones wrote: > >On 20 Mar 2006, at 15:14, Igor Tandetnik wrote: > >> Ian M. Jones <ian-if8tMcB8Y5RWk0Htik3J/[EMAIL PROTECTED]> wrote: >>> I'm trying to get a count of all Cases for each Category, with an >>> outer join to Cases so that I always get a record for each Category >>> regardless of whether there are any Cases with that Category or not. >>> >>> select xcat.Category, count(xc.CaseID) as NumCases >>> from Category as xcat >>> left join Cases as xc on xcat.CategoryID = xc.CategoryID >>> where xc.CaseID in (3145) >>> group by xcat.Category >>> order by xcat.Category >>> ; >> >> How many cases with an ID of 3145 do you expect to find? In other >> words, what's the purpose of the 'where' clause in your query? >> > >In the real query there is a need for the where clause, it's a way of >determining how many cases exist in each category for proper query >(the in clause contains a proper query). > >In this test example I'm just using CaseID 3145 to make sure that >only one category is matched, because I want to make sure the other >two categories still come back with a count of 0. > >But I'm not getting the other two categories coming back at all, let >alone with a count of 0. >-- >Ian M. Jones >___________________________________ >IMiJ Software >http://www.imijsoft.com >http://www.ianmjones.net (blog) > > > Sincerely, Ed Porter
