Something equivalent to "SELECT * FROM (SELECT table_name FROM...)"?

Question

This query runs, but it produces the name of a table as a result, rather than actually selecting from that table.

SELECT T.* 
  FROM (SELECT tablename 
          FROM ListOfTables 
         WHERE id = 0) AS T

where ListOfTables contains id=0, tablename='some_table', I want to return the same result set as if I had written this directly:

SELECT * FROM some_table

Is there a native way to do this in MySQL 5, or do I have to do in in the application?

Answer 1

To do this in MySQL, you need to create a prepared statement which you can only create from a user variable:

SELECT @tn := tablename FROM ListOfTables WHERE id = 0;

SET @qs = CONCAT('SELECT * FROM ', @tn);
PREPARE ps FROM @qs;
EXECUTE ps;