Andy Thompson Andy Thompson - 3 months ago 14
MySQL Question

MySQL select from tables returned by query

I am trying to select from MySQL tablenames returned by a query. The tablenames are table_prefix_date (e.g. table_prefix_20160801).

To get all the dates and corresponding tablenames I do something like this:

select concat('db_name.table_prefix_',a.Date)
from (
select date_format(curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY,'%Y%m%d') as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where date_format(Date,'%Y-%m-%d') between '2016-08-01' and '2016-08-20'
order by 1 asc;


All the tables have the same columns and indexes. Just doing
select * from (queryabove);
fails.

Can somebody please give me some working example of how I can retrieve data from the tables returned by the query above?

Answer

You need to execute a dynamic SQL statement:

set @sql = (
  select group_concat(replace('select * from @table', '@table', 
                               concat('table_prefix_', date_format(a.Date, '%Y%m%d'))
                             ) separator ' union all ')
  from (select date_sub(curdate(), INTERVAL (a.a + (10 * b.a) + (100 * c.a)) day) as Date
        from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a cross join
             (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b cross join
             (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
       ) a
  where Date between '2016-08-15' and '2016-08-18'
);

prepare stmt1 from @sql;
execute stmt1; 

Here is a SQL Fiddle for the above.

The query is essentially your query with the select and union all logic.

More importantly, though, you should not be designing a set of tables that contain the same information. Instead, you should have a date column in a big table that you insert into.