Sorry, You are correct... Based on what you said, there can be many
cases for one category. But you might have some cases that do not
point to a Category since the foreign key could be null. So you are
trying to find for each category what number of cases there are.
Here is where I missing something, you had mentioned --"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."--, since you
are specifying one case, that case should only point back to one
category, why would there be two other rows?
Thanks,
Calvin
On Mar 20, 2006, at 8:42 AM, Ian M. Jones wrote:
On 20 Mar 2006, at 14:36, Calvin HIll wrote:
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.
"left join" is short for "left outer join", just as "join" on it's
own is short for "inner join".
I've tried "left outer join" anyway, still the same result, no
outer functionality.
Any other ideas?
--
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>
