List:General Discussion« Previous MessageNext Message »
From:Nuno Tavares Date:January 12 2011 2:58pm
Subject:Re: how to generate a data set then join with in on fly?
View as plain text  
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?
> 
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