List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:July 3 2004 1:53am
Subject:Re: How to get count(id) when count is zero?
View as plain text  
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
>
>
>  
>


Thread
How to get count(id) when count is zero?Nishikant Kapoor2 Jul
  • Re: How to get count(id) when count is zero?Martijn Tonies2 Jul
  • Re: How to get count(id) when count is zero?Michael Dykman3 Jul