This should work
select C.cId cc, count(D.catId) dd from C
left outer join D on C.cId = D.catId
group by cc;
- michael dykman
- michael@stripped
Kapoor, Nishikant wrote:
>Hello List,
>
>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 am using
> mysql Ver 12.21 Distrib 4.0.15, for mandrake-linux-gnu (i586)
>on Mandrake Linux, 9.1.
>
>
>Thanks,
>Nishi
>
>
>
>
