From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: 2 Joins in 1 Query
Date: Mon, 23 May 2005 12:20:05 -0400
"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 05/23/2005
10:18:29 AM:
> Hi,
>
> The following query produces the number of bookings per project grouped
by
> week:
>
> 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.
>
> However I am not sure how I can join Weeks to Bookings so that all the
weeks
> show.
>
> Any healp would be greatly appreciated.
>
> TABLE DEF'S:
>
> mysql> desc Bookings;
> +---------------------------------------------+-------------+------
> +-----+---------------------+----------------+
> | Field | Type | Null | Key
|
> Default | Extra |
> +---------------------------------------------+-------------+------
> +-----+---------------------+----------------+
> | Booking_ID | int(11) | | PRI
|
> NULL | auto_increment |
> | Booking_Type | varchar(15) | | |
> Unavailability | |
> | User_ID | int(11) | | | 0
> | |
> | Project_ID | int(11) | YES | |
> NULL | |
> | Rep_ID | int(11) | YES | |
> NULL | |
> | Practice_ID | int(11) | YES | |
> NULL | |
> | Booking_Creator_ID | int(11) | YES | |
> NULL | |
> | Booking_Creation_Date | datetime | YES | |
> NULL | |
> | Booking_Start_Date | datetime | | |
> 0000-00-00 00:00:00 | |
> | Booking_End_Date | datetime | | |
> 0000-00-00 00:00:00 | |
> | Booking_Completion_Date | date | YES | |
> NULL | |
> | Booking_Mileage | int(5) | YES | |
> NULL | |
> | Booking_Status | varchar(15) | | |
> Other | |
> | Unavailability_ID | int(2) | YES | |
> NULL | |
> | Task_ID | int(11) | YES | |
> NULL | |
> | Work_Type_ID | int(2) | YES | |
> NULL | |
> | Additional_Notes | text | YES | |
> NULL | |
> +---------------------------------------------+-------------+------
> +-----+---------------------+----------------+
> 22 rows in set (0.00 sec)
>
> mysql> desc Projects;
> +----------------------------+--------------+------+-----+---------
> +----------------+
> | Field | Type | Null | Key | Default |
Extra
> |
> +----------------------------+--------------+------+-----+---------
> +----------------+
> | Project_ID | int(11) | | PRI | NULL |
> auto_increment |
> | Project_Name | varchar(100) | | | |
> |
> | Client_ID | int(11) | | | 0 |
> |
> +----------------------------+--------------+------+-----+---------
> +----------------+
> 8 rows in set (0.00 sec)
>
> mysql> desc Weeks;
> +-------------+---------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+---------+------+-----+---------+----------------+
> | Week_ID | int(11) | | PRI | NULL | auto_increment |
> | Week_Number | int(11) | | | 0 | |
> +-------------+---------+------+-----+---------+----------------+
> 2 rows in set (0.00 sec)
>
> mysql>
>
>
I think you need just think about what you want and what may or may not
exist as data, then you can figure out which JOINs are LEFT and which are
INNER. You want one row for each week regardless of whether you have a
Project or a Booking. That makes the Weeks table manditory. There may be
weeks that do not have any Bookings. That makes Bookings the right side of
a LEFT JOIN. Because the existence of a Projects is dependent on the
existence of a Booking, it too is LEFT JOINED into the query. If you want
to ensure that you only get records that have Projects, check for a
Project-table value in your WHERE clause. That means the <table reference>
portion of your query should look like
And that means your whole query translates to:
SELECT w.Week_Number 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 Weeks w
LEFT Bookings b
on WEEK(b.Booking_Start_Date) = w.Week_Number
AND b.Booking_Type = "Booking"
LEFT JOIN Projects p
ON p.Project_Id = b.Project_ID
WHERE B.Booking_Start_Date >= '2005-01-01'
AND B.Booking_Start_Date < '2006-01-01'
GROUP BY WEEK;
I moved your check for Booking_Type = "Booking" into your JOIN so that
there is the possibility of using an INDEX during the JOIN ( I could have
possibly moved your date range checks there too. You might also try that,
just to see which query works faster)
I also changed your WHERE conditions to compare date values to date
values. You were converting everything into strings which is doing it the
hard way. A date value with no time value equates to MIDNIGHT of that day.
I hope you can see why my WHERE clause is equivalent to your old one
except I believe mine will work much faster as there could be an index on
Booking_Start_Date. (Next time, use SHOW CREATE TABLE xxxx\G instead of
DESCRIBE xxx. The output contains all indexes and foreign keys not just
the column definitions)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Hi Shaun,
Thanks for your reply. The query works fine however I have to take out the
following line:
AND B.Booking_Type = 'Booking'
If it is left in i get missing weeks. I have tried it in the following ways:
SELECT w.Week_Number 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 Weeks w
LEFT JOIN Bookings B
ON WEEK(B.Booking_Start_Date) = w.Week_Number
AND B.Booking_Type = 'Booking'
LEFT JOIN Projects P
ON P.Project_ID = B.Project_ID
WHERE B.Booking_Start_Date >= '2005-01-01'
AND B.Booking_Start_Date < '2006-01-01'
GROUP BY WEEK;
SELECT w.Week_Number 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 Weeks w
LEFT JOIN Bookings B
ON WEEK(B.Booking_Start_Date) = w.Week_Number
LEFT JOIN Projects P
ON P.Project_ID = B.Project_ID
WHERE B.Booking_Start_Date >= '2005-01-01'
AND B.Booking_Start_Date < '2006-01-01'
AND B.Booking_Type = 'Booking'
GROUP BY WEEK;
Do you have any ideas why this is happening?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
