On 1/10/2011 18:51, Ryan Liu wrote:
> 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 ?
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.
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 Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN