List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 20 2004 1:22pm
Subject:Re: SELECTING Non existing Dates
View as plain text  
With a LEFT JOIN you will get back your "zero hours" only if there exist 
records in the Timesheets table that match no records in the projects 
table.  If you have no Timesheets data for the 23rd or 24th, then you 
can't have any rows in your results for those dates.  MySQL won't fill in 
the missing dates for you.

I have had to resort to writing a script that builds a temporary table 
that contains a complete range of the dates I need for a query (the 
results are to be used to make a graph) just so that I can have blank 
values for those dates where no data exists.  Others on the list may have 
better ideas on how to fill in missing data (like your missing dates) so I 
only offer my idea as a suggestion, not a solution.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"shaun thornburgh" <shaunthornburgh@stripped> wrote on 10/20/2004 
08:28:53 AM:

> Hi,
> 
> I have query that I am using to try to get the number of hours worked 
and on 
> which project by an employee during a particular week. Here is the 
query:
> 
> SELECT T.Date, T.Hours, P.Project_Name
> FROM Timesheets T
> LEFT JOIN Projects P ON P.Project_ID = T.Project_ID
> WHERE T.Date >= "2004-10-18"
> AND T.Date <= "2004-10-24"
> AND T.User_ID = "1"
> AND T.Type = "Project"
> GROUP BY T.Date, T.Project_ID
> ORDER BY T.Date
> 
> And here is the result:
> 
> +------------+-------+--------------+
> | Date       | Hours | Project_Name |
> +------------+-------+--------------+
> | 2004-10-18 |     4 | Project A    |
> | 2004-10-18 |     3 | Project B    |
> | 2004-10-19 |     3 | Project A    |
> | 2004-10-19 |     5 | Project B    |
> | 2004-10-20 |     4 | Project A    |
> | 2004-10-20 |     3 | Project B    |
> | 2004-10-21 |     1 | Project A    |
> | 2004-10-21 |     7 | Project B    |
> | 2004-10-22 |     2 | Project A    |
> | 2004-10-22 |     6 | Project B    |
> +------------+-------+--------------+
> 
> 
> All is working fine except I would like the query to show zero values 
for 
> days where there are no entries i.e. the 23rd and 24th in this example. 
I 
> thought that the LEFT JOIN would achieve this but unfortunately not! If 
> someone could show me where I am going wrong I would be most grateful.
> 
> Many thanks,
> 
> 
> 
> Shaun
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
SELECTING Non existing Datesshaun thornburgh20 Oct
  • Re: SELECTING Non existing DatesMartijn Tonies20 Oct
  • Re: SELECTING Non existing DatesSGreen20 Oct
  • Re: SELECTING Non existing DatesHarald Fuchs20 Oct
Re: SELECTING Non existing Datesshaun thornburgh21 Oct