Hi,
> It sounds like a simple query but I have spent quite a few hours already,
and still do not have a solution. Would greatly appreciate your response.
>
> Here is what I am trying to do:
> -------------------------------
> create table C (cId tinyint(4) NOT NULL);
> insert into C values (1), (2), (3), (4), (5);
>
> create table D (id tinyint(4) NOT NULL, catId tinyint(4) NOT NULL);
> insert into D values (1,2), (2,2), (3,2), (4,1), (5,1);
>
> select C.cId, count(C.cId) from C, D where C.cId = D.catId group by C.cid;
>
> This returns:
> -------------
>
> | cId | count(C.cId) |
> +-----+--------------+
> | 1 | 2 |
> | 2 | 3 |
> +-----+--------------+
> 2 rows in set (0.01 sec)
>
> ----------------------------
> What I would like to see is:
> ----------------------------
>
> | cId | count(C.cId) |
> +-----+--------------+
> | 1 | 2 |
> | 2 | 3 |
> | 3 | 0 |
> | 4 | 0 |
> | 5 | 0 |
I'm unsure why Count(C.cID) should be 0 if you're
counting C.CIDs...
This returns what you want, but it counts catID in D:
select C.cId, count(D.catId)
from C left outer join D on C.cId = D.catId
group by C.cid
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
