Hi Ryan. That's a common issue for reporting. This post might have you an idea where to generate those inexistent dates (time slots), just forget about the specific aggregates and partitioning done in there:
http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/ -- Nuno Tavares DRI, Consultoria Informática Telef: +351 936 184 086 Shawn Green (MySQL) escreveu: > On 1/10/2011 18:51, Ryan Liu wrote: >> Hi, >> >> In MySQL, is that possible to generate a data set join with it on fly >> (without >> create an temporary table)? >> >> e.g. for a report used by a graphic tool, it requires data in all >> dates, even it >> is null. Can I >> >> select vacationT.* left join ( all dates d in the past 3 years) on >> vacationT.`date` = d ? >> >> Thanks, >> Ryan >> > > Sorry, no. To do the report I think you are describing will require you > to have a table of all dates. Also the date table needs to be on the > LEFT side of the LEFT JOIN to be included even if there aren't any matches. > > SELECT .... > FROM master_date_table LEFT JOIN vacationT ... > > Or, you can accept the partial list of dates actually stored in the > database as accurate and fill in any missing dates when you render it in > your report (inside the application). It may be much easier to fill-in > those dates when you format the report, have you checked? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
