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.

asked Sep 13, 2017 at 2:30

takobaba's user avatar

1

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.

Nimantha's user avatar

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered Sep 13, 2017 at 2:32

Gordon Linoff's user avatar

1 Comment

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's user avatar

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's user avatar

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.