In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] writes:
> 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.
I'm using roughly the same technique, with a small variation: my
helper table is not temporary, but permanent, and contains just the
integers 1 .. 1000:
CREATE TABLE seq (val INT PRIMARY KEY);
INSERT INTO seq VALUES (1), (2), ..., (1000);
(You could use any range of values you need.)
The join now looks like this:
SELECT '2004-10-17' + INTERVAL s.val DAY AS day,
T.Hours, P.Project_Name
FROM utildb.seq s
LEFT JOIN Timesheets T ON T.Date = '2004-10-17' + INTERVAL s.val DAY
LEFT JOIN Projects P ON P.Project_ID = T.Project_ID
WHERE s.val BETWEEN 1 AND 7
AND T.User_ID = "1"
AND T.Type = "Project"
GROUP BY day, T.Project_ID
ORDER BY day
The disadvantage is that the join expression is more complicated, but
I don't have to mess with temp tables.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
