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)
