"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