Hi, I am relatively new to MySQL.. I am having trouble getting an aggregate function and an outer join to work together. I have two tables PROJECT (id_project, nm_project) 100, prj1 101, prj2 DAYLOG (id_project, date, hours) 100, 2002-08-17, 8 100, 2002-08-18, 8 DAYLOG table contains number of hours booked against a project for a date. I needed to list the total hours booked against each project for any given month. My query SELECT p.id_project, p.nm_project, sum( dl.hours) FROM PROJECT p LEFT OUTER JOIN DAYLOG dl ON (p.id_project = dl.id_project) WHERE MONTH(dl.DATE) = 8 AND YEAR(dl.DATE) = 2002 GROUP BY p.id_project +------------+------------+------------------------------------+ | id_project | nm_project | sum( dl.hours) | +------------+------------+------------------------------------+ | 100 | prj1 | 16 | +------------+------------+------------------------------------+ The problem with this result set is that it does not have projects with zero hours booked. ie. . if there are no records in DAYLOG, then that project is not displayed. I was expecting the left outer join to pick up records (prj2) from PROJECT even if there are no records in DAYLOG. So I tried another variation with the select SELECT p.id_project, p.nm_project, sum( dl.hours)+ IFNULL(dl.hours,0) added the IFNULL. Yet the query lists only projects which have records for the given month. Any insight, help, thoughts etc are greatly appreciated. Thanks, -Manu --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
