Variable table name in SELECT statement

Dev Shed Forums Sponsor:

December 21st, 2012, 08:14 PM

mmmosias

Registered User

Join Date: Aug 2011

Posts: 18

Time spent in forums: 2 h 36 m 55 sec
Reputation Power: 0

Variable table name in SELECT statement

What is the way to write a SELECT statement to retrieve data from several tables with the same name pattern. I'd like to select data from these tables: table_2012, table_2013, table_2014... I only want the tables that have the prefix (e.g. table_) and exactly the four characters for the year. I don't want tables that have a different pattern than the year. Is this doable in MySQL? What is the

SELECT * FROM TABLE LIKE table_% WHERE ....

December 21st, 2012, 08:49 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,714

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 7 h 5 m 48 sec
Reputation Power: 8969

Send a message via Google Talk to requinix

Not like that no.

The real problem is that you have a bunch of tables all for specific years. You really should be storing everything in one table, and including an extra column to indicate the year (if there isn't already something you can use).

You may be able to create VIEWs for each of the tables you're deprecating.

Code:

CREATE VIEW `table_2012` AS SELECT * FROM `table` WHERE `yearValue` = 2012;

If that doesn't work I'd even consider using triggers (per-year tables have INSERT/UPDATE triggers to copy the action in the main table, main table has basically the same but going into the per-year tables) just so that you can collect all the data in one master location.

But to answer the question you can do a UNION between all of them. (Still have to write the queries though.)

Code:

SELECT ...
UNION ALL
SELECT ...
UNION ALL
SELECT ...

With a "UNION" alone MySQL will try to remove duplicates for you. If you want them, or know that there won't be any, "UNION ALL" will instruct it not to do so.

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

Last edited by requinix : December 21st, 2012 at 08:53 PM.

December 22nd, 2012, 10:23 AM

mmmosias

Registered User

Join Date: Aug 2011

Posts: 18

Time spent in forums: 2 h 36 m 55 sec
Reputation Power: 0

These are all good suggestions. But I was wondering whether there was a way to define the SELECT statement to look for tables with a certain pattern in their name, similar to the LIKE feature for columns.

Thanks.

Quote:

Originally Posted by requinix

Code:

CREATE VIEW `table_2012` AS SELECT * FROM `table` WHERE `yearValue` = 2012;

Code:

SELECT ...
UNION ALL
SELECT ...
UNION ALL
SELECT ...

With a "UNION" alone MySQL will try to remove duplicates for you. If you want them, or know that there won't be any, "UNION ALL" will instruct it not to do so.

December 22nd, 2012, 10:59 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 33 m 50 sec
Reputation Power: 4140

Quote:

Originally Posted by mmmosias

But I was wondering whether there was a way to define the SELECT statement to look for tables with a certain pattern in their name, similar to the LIKE feature for columns.

there isn't

you could do it with an application language like php, by reading the information_schema tables to build the query, but that's two separate steps

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Variable table name in SELECT statement

Developer Shed Advertisers and Affiliates