Hi, > 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'm unsure why Count(C.cID) should be 0 if you're counting C.CIDs... This returns what you want, but it counts catID in D: select C.cId, count(D.catId) from C left outer join D on C.cId = D.catId group by C.cid With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
