SELECT statement with wildcard in table name

[mmmosias]

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 ....

[Atli]

Hey. This question suggests a fairly poor database design; a sort of Excel-like thinking when constructing the tables. (Relational databases are NOT spreedsheet applications, and should not be used as such!)

I like to say that data should go into tables, not become tables. What I mean by that is that you should never have to put the data itself into table or column names. The database structure should be fixed, and any new data should be placed into the tables that already exists.

For instance, say that you had these tables:

Table: work_log_2010
+----------+-----+-----+-----+-----+
| employee | jan | feb | ... | dec |
+----------+-----+-----+-----+-----+
| John     |  35 |  36 | ... |  24 |
| Smith    |  48 |  49 | ... |  36 |
| Beth     |  29 |  36 | ... |  31 |
+----------+-----+-----+-----+-----+

Table: work_log_2011
+----------+-----+-----+-----+-----+
| employee | jan | feb | ... | dec |
+----------+-----+-----+-----+-----+
| John     |  35 |  36 | ... |  24 |
| Beth     |  48 |  49 | ... |  36 |
| Smith    |  29 |  36 | ... |  31 |
+----------+-----+-----+-----+-----+

Table: work_log_2012
+----------+-----+-----+-----+-----+
| employee | jan | feb | ... | dec |
+----------+-----+-----+-----+-----+
| Beth     |  35 |  36 | ... |  24 |
| Smith    |  48 |  49 | ... |  36 |
| John     |  29 |  36 | ... |  31 |
+----------+-----+-----+-----+-----+

These are great when using Excel, having each year in a separate sheet, with columns for employees and months. But this is horrible when using MySQL. It complicates both the maintenance of the tables and the queries required to access it. Especially if you need to get or filter by the year or months.

For a relational database like MySQL, you want the above to look more like this:

Table: work_log
+----------+------------+-------+
| employee | date       | hours |
+----------+------------+-------+
| John     | 2010-01-01 |    35 |
| Smith    | 2010-01-01 |    48 |
| Beth     | 2010-01-01 |    29 |
| John     | 2010-02-01 |    36 |
| Smith    | 2010-02-01 |    49 |
| Beth     | 2010-02-01 |    36 |
| etc...   | ...        |    .. |
+----------+------------+-------+

This table is easy to use in code, and it doesn't require any modifications to the table structure when new years are started. All the data goes into a single table, and that includes ALL the data, including the year and the months. You won't have to do some crappy workarounds to find or filter the years and months in this table.