"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 05/24/2005
07:35:03 AM:
> >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 mandatory. 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?
>
>
I am afraid I do. I was in such a rush to finish the query I forgot to
"logic-check" myself against your needs. If a table is "optional" in a
query then it will have NULL values for the rows where it doesn't match
up. What I did was _require_ data in the final results by putting some
conditions on that optional table (Bookings) in the WHERE clause. That
meant that you can't get any rows where Bookings.<anycolumn> is null (the
weeks without bookings). D'OH!
Try it this way
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'
AND B.Booking_Start_Date >= '2005-01-01'
AND B.Booking_Start_Date < '2006-01-01'
LEFT JOIN Projects P
ON P.Project_ID = B.Project_ID
GROUP BY WEEK;
Doing it this way, you will see every row of your WEEKS table plus
optionally any matching data from your Bookings table for 'Booking'
records within a certain date range, and any Projects that match to those
Bookings.
In order to limit your results to a certain range of weeks, add a WHERE
clause like:
WHERE w.Week_Number BETWEEN 26 and 53
Make sense? I should have thought it through a little better and avoided
this confusion.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
