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
>
