List:General Discussion« Previous MessageNext Message »
From:Nishikant Kapoor Date:July 2 2004 1:30pm
Subject:How to get count(id) when count is zero?
View as plain text  
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