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
- [EMAIL PROTECTED]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
