Hi,
Unfortunately that doesnt work, I need to LEFT JOIN Bookings to Weeks but
keep the current join on Projects there as well, any ideas?
From: "Peter Normann" <[EMAIL PROTECTED]>
To: "'shaun thornburgh'"
<[EMAIL PROTECTED]>,<mysql@lists.mysql.com>
Subject: RE: 2 Joins in 1 Query
Date: Mon, 23 May 2005 17:13:42 +0200
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
