MySQL Loop in Union and select * I am using redash for mySQL data source and trying to create a dashboard.
I have many table with the name as tablename_2021_01_01, tablename_2021_01_02, tablename_2021_01_03, tablename_2021_01_04 and so on. All these tables contain data in the same format. Now i want to pull data from 30 tables in one shot. How to do it?
Hopefully, today you learned that the &quot;Table-per-Day&quot; model for storing your data is a Bad Idea.
Generally speaking, &quot;Table-per-anything&quot; is a poor choice these days.
With proper indexing, there is absolutely no reason why all of this data couldn't live in just one table.
Also, I'm pretty sure MySQL still has the hard limit for the number of tables in a query (including those in any Views used) of [just] 61 tables, which is only double what you're currently asking for.
As to your immediate question, &quot;union all&quot; is your friend.
select * from tablename_2021_01_01
union all 
select * from tablename_2021_01_02
union all 
select * from tablename_2021_01_03
. . . 
where ...