CODEWITHSUNDEEP
mysqlselectdynamic-sql
Andy Thompson
Andy Thompson

Reputation: 304

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?

Upvotes: 0

Views: 225

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

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.

Upvotes: 3

Related Questions