I have not messed with SQLlite that much (just some simple tables, no
joining). But I have work with Oracle, Sybase, and a little DB2.
The standard for a left outer join using your query below would be:
select xcat.Category, count(xc.CaseID) as NumCases
from Category as xcat
left outer join Cases as xc on xcat.CategoryID = xc.CategoryID
where xc.CaseID in (3145)
group by xcat.Category
order by xcat.Category;
It looks like you forgot the 'outer' part of the syntax. But based
on what I read on what you want, that is correct. You do want a left
outer join.
Regards,
Calvin
On Mar 20, 2006, at 6:34 AM, Ian M. Jones wrote:
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)
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
