1

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
    );
2

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.

  • I think it should be possible to avoid a cursor with something like GROUP_CONCAT(CONCAT('SELECT * FROM ', table_name) SEPARATOR ' UNION ALL '). – Andriy M Aug 22 '12 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.