I'm trying to group a query by intervals in this way:
SELECT COUNT( * ) AS count, i.date
FROM intervals i
LEFT JOIN dtracker dt ON
i.date <= dt.date AND i.date + INTERVAL (NUMSEC) SECOND >= dt.date
WHERE (SOME CONDITIONS)
GROUP BY i.date
where I fill the table 'intervals' each time (cause intervals can change), and NUMSEC is changing as well depending of the intervals table.
Everything is working fine without 'WHERE', if there´s not results in some intervals, it shows 0 intervals. But if I put some conditions in 'WHERE' and there's no results, it doesn't show this interval anymore.
Seems like 'WHERE' clauses affects the whole query (it makes sense) but I need it to affect only to dtracker table. How I can do this?
Thanks a lot in advance.
Replace WHERE with another AND so that the conditions become part of the LEFT JOIN ... ON clause.
Maybe you'll be interested by using the 'HAVING' keyword.