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?

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to