OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
 
How to get count(id) when count is zero?

From: Kapoor, Nishikant (nishikant.x.kapoorxcelenergy.com)
Date: Fri Jul 02 2004 - 08:30:31 CDT

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql