shaun thornburgh <mailto:[EMAIL PROTECTED]> wrote:
> SELECT WEEK(Booking_Start_Date) AS "WEEK", > SUM(IF(B.Project_ID = "23", 1,0)) AS `Project A`, > SUM(IF(B.Project_ID = "42", 1,0)) AS `Project B` > FROM Bookings B INNER JOIN Projects P USING (Project_ID) > WHERE B.Booking_Type = "Booking" > AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") >= '2005-01-01' > AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") <= '2005-12-31' > GROUP BY WEEK; > > The problem with this is that if I have no bookings for week 42 for > example then that week is not shown in the result. To get round this > I created a table called Weeks that contains all the week numbers for > the year. Try a LEFT JOIN: SELECT WEEK(Booking_Start_Date) AS "WEEK", SUM(IF(B.Project_ID = "23", 1,0)) AS `Project A`, SUM(IF(B.Project_ID = "42", 1,0)) AS `Project B` FROM Bookings B LEFT JOIN Projects P ON (P.Project_ID = B.Project_ID) WHERE B.Booking_Type = "Booking" AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") >= '2005-01-01' AND DATE_FORMAT(B.Booking_Start_Date, "%Y-%m-%d") <= '2005-12-31' GROUP BY WEEK; Regards Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
