how to generate a data set then join with in on fly?

--0-572664563-1294703489=:9998
Content-Type: text/plain; charset=us-ascii

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



--0-572664563-1294703489=:9998--

Re: how to generate a data set then join with in on fly?

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=gcdmg-mysql-2@m.gmane.org

Re: how to generate a data set then join with in on fly?

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-ti me-interval-how-many-records-are-ocurring-during-that-interv al/

--
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=gcdmg-mysql-2@m.gmane.org