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-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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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=arch...@jab.org
how to generate a data set then join with in on fly?
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
