"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

Reply via email to