Hi guys and gals,
I'm either going mad or having a very bad day, but the following
isn't doing what I'd expect and could do with some kind soul putting
me right.
I have a table called Category which has a primary key column called
CategoryID, and three values in the Category column, "Inquiry", "Bug"
and "Feature".
I have a second table called Cases, this table has a column called
CaseID which is the primary key and a CategoryID column as a foreign
key to Category.
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.
Simple stuff you'd think, but it isn't working. Here's my SQL:
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
;
This is a vastly simplified query, I'm using a specific CaseID so
that I only get one category joined, the other two should come back
with a count of 0. But all I'm getting is the one record that joins.
Inquiry|1
What am I doing wrong, why isn't the (left) outer join working?
Thanks,
--
Ian M. Jones
___________________________________
IMiJ Software
http://www.imijsoft.com
http://www.ianmjones.net (blog)
