How do I perform a UNION query on a dynamic list of tables?

Question

My database has an unknown amount of tables, named with the same prefix followed by a random string: _ct_<random_string>.

Each of these tables has the same data structure.

I also have another table in which I store the name of each table for future reference.

The following table name is 'content_types' and provides reference for other tables:

+-----------------+
| id | table_name |
+-----------------+
| 1  | _ct_table1 |
| 2  | _ct_table2 |
| 3  | _ct_table3 |
| 4  | _ct_fdfsf  |
| 5  | _ct_535dgf |
...
+-----------------+

The following are example to the referenced tables in 'content_types'.'table_name':

_ct_table1:                              _ct_table2:
+-----------------------------------+    +-----------------------------------+
| id | title | subtitle | date      |    | id | title | subtitle | date      |
+-----------------------------------+    +-----------------------------------+
| 1  | fddfs | ...      | 2012-08-22|    | 4  | fddfs | ...      | 2012-03-18|
| 2  | bdsf  |          | 2012-08-22|    | 5  | b4sf  | dfs      | 2012-07-20|
| 3  | sfdsd |          | 2012-08-22|    | 6  | sfdsd |          | 2012-03-12|
+-----------------------------------+    +-----------------------------------+

Is it possible to create a query with a dynamic UNION statement based on the tables listed in 'content_types'.'table_name'?

Something like this:

select id,title,subtitle,date 
from 
    (
        select table_name 
        from content_types
    );

Answer 1

Yes you can. There are several ways to do this.

1> If you are using the database interactively, then just type in the query. 2> If the database is being accessed via a application or script, write a method in the application/script to create the query required and then run it. You would have a database call to get the list of table names, then create a string with the union query then execute that query. 3> In a similar vein to option 2, most stored procedure languages allow you to dynamically create a query as a string and then execute that string - Check out your DB's documentation to find out how to do this.

A Mysql example https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure. You may like to use a cursor ( http://dev.mysql.com/doc/refman/5.0/en/cursors.html ) to iterate though the list of tables names to Concat ( http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat ) together the query string.