On 7 Nov 2005, at 01:06, [EMAIL PROTECTED] wrote:
A) a database should not respond with data it does not have.
Well, it's not really - it's returning a count of 0 for a particular
match condition. I could achieve the same result by saying:
SELECT DATE_FORMAT(event.timestamp, '%Y-%m-%d') AS adate, COUNT
(event.id) AS eventcount
FROM issue, `session`, event
WHERE `session`.issue_id = issue.id
AND event.session_id = `session`.id
AND DATE_FORMAT(event.timestamp,
'%Y-%m-%d') >= '2005-10-01'
AND event.eventtype = '$eventtype'
and repeating it for each date I want to know about. But I'm sure it
can be done in one query somehow.
You suggest creating a "set of values" for the GROUP BY
statement... how
is that functionally different than using another table and LEFT
joining?
Because the set of values could be generated dynamically and passed
in the query and thus would not have to be present in the DB. I'm
thinking along the lines of:
GROUP BY FIELD(DATE_FORMAT(event.timestamp, '%Y-%m-%d'),
'2005-10-01','2005-10-02','2005-10-03','2005-10-04','2005-10-05','2005-1
0-06')
That syntax is lifted from ORDER BY, is something similar available
for GROUP BY?
Marcus
--
Marcus Bointon
Synchromedia Limited: Putting you in the picture
[EMAIL PROTECTED] | http://www.synchromedia.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
