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" <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] >
