List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:January 11 2011 4:26pm
Subject:Re: how to generate a data set then join with in on fly?
View as plain text  
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
Thread
how to generate a data set then join with in on fly?Ryan Liu11 Jan
  • Re: how to generate a data set then join with in on fly?MySQL)11 Jan
    • Re: how to generate a data set then join with in on fly?Nuno Tavares12 Jan