How to query multiple SQL tables with the same table schema?

Question

I have below list of tables which is created based on weekly archive.

dbtable
dbtable_01_04_2017
dbtable_01_07_2017
dbtable_02_09_2017
dbtable_03_06_2017
dbtable_05_08_2017
dbtable_06_05_2017
dbtable_08_04_2017
dbtable_08_07_2017
dbtable_09_09_2017
dbtable_10_06_2017
dbtable_12_08_2017
dbtable_13_05_2017
dbtable_15_04_2017
dbtable_15_07_2017
dbtable_17_06_2017
dbtable_19_08_2017
dbtable_20_05_2017
dbtable_22_04_2017
dbtable_22_07_2017
dbtable_24_06_2017
dbtable_26_08_2017
dbtable_27_05_2017
dbtable_29_04_2017
dbtable_29_07_2017

All of them has the exact same column structure:

TIME_STAMP, USERNAME, INFO, CAUSE

I need to query all of them at once using the same query, of course only by changing the table name in below example query.

 SELECT * FROM dbtable WHERE USERNAME="XXXX";

I googled a bit hoping that there is a way to wildcard the name in the query but I learned that SQL doesn't support that.

Basically, I am looking for some kind of for loop to iterate the query to multiple tables.

Please note that new tables will be added each week based on our archive automation.

Please justify having weekly tables. Then we will try to explain why there is no advantage in such. — Rick James
– Rick James, Commented Sep 13, 2017 at 3:13

Nimantha · Accepted Answer · 2022-01-05 11:47:57Z

3

The problem is your data structure. Multiple tables with the same format is generally a bad idea. You should have a single table, with a column for the date.

Oh, wait, you are concerned about performance, because each of the existing tables is big. That is why (most) databases support partitioning. You can read about it in the documentation.

If you don't go down that route, one possibility is to create a view with all the tables -- and then when you add a table alter the view to include the latest table. Of course, queries will probably end up reading all the tables, so they will not be very efficient.

The right solution is to use a single table with multiple partitions.

edited Jan 5, 2022 at 11:47

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Sep 13, 2017 at 2:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

Probably the only justification for partitioning by time is if you intend to purge 'old' data. See mysql.rjweb.org/doc.php/partitionmaint

takobaba · Accepted Answer · 2017-12-15 00:54:34Z

2

UNION ALL is the final solution.

SELECT * FROM dbtable WHERE `USERNAME` LIKE '$variable' UNION ALL 
SELECT * FROM dbtable_01_04_2017 WHERE `USERNAME` LIKE '$variable' UNION ALL 
SELECT * FROM dbtable_01_07_2017 WHERE `USERNAME` LIKE '$variable' UNION ALL 
SELECT * FROM dbtable_02_09_2017 WHERE `USERNAME` LIKE '$variable' UNION ALL 
SELECT * FROM dbtable_03_06_2017 WHERE `USERNAME` LIKE '$variable' UNION ALL 
SELECT * FROM dbtable_05_08_2017 WHERE `USERNAME` LIKE '$variable' UNION ALL 
SELECT * FROM dbtable_06_05_2017 WHERE `USERNAME` LIKE '$variable' UNION ALL 
SELECT * FROM dbtable_08_04_2017 WHERE `USERNAME` LIKE '$variable'

I was able to achieve what I need by using above SQL query.

answered Dec 15, 2017 at 0:54

takobaba

3141 gold badge6 silver badges17 bronze badges

Comments

kmoser · Accepted Answer · 2017-09-13 02:56:17Z

0

You could write a stored procedure that queries which tables exists and performs the SELECT on those tables.

answered Sep 13, 2017 at 2:56

kmoser

9,3693 gold badges28 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to query multiple SQL tables with the same table schema?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related