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?

Table names in SQL can not be dynamic -- you need to use dynamic SQL, which no one can provide a reasonable example of until you specify what database you're using. — OMG Ponies, Feb 26 '12 at 16:13
Native way to do what? It's not clear what you are trying to achieve. — Andrew Logvinov, Feb 26 '12 at 16:14
What result are you expecting ? Please show structure and some content of table 'ListOfTables' — Vitalii Lebediev, Feb 26 '12 at 16:15

score 5 · Answer 1 · answered Feb 26 '12 at 16:26

5

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;

answered Feb 26 '12 at 16:26

AndreKR

32,613
18
106
168

What about where `id` is a parameter? – spraff Feb 26 '12 at 16:35
I don't understand. Of course you can feed in the id value from your application like usual. You can even make the first query a prepared statement, too. – AndreKR Feb 26 '12 at 16:36
I need to make it all a single statement for PHP's `mysql_query` -- I'm playing around with it but can't find a way to fold everything together without a parse error. – spraff Feb 26 '12 at 16:41
This is because, to cite http://php.net/manual/en/function.mysql-query.php, multiple queries are not supported. You have to use a different client API, make a stored procedure or find another solution, maybe in http://stackoverflow.com/questions/345637/php-multiple-sql-queries-in-one-mysql-query-statement – AndreKR Feb 26 '12 at 16:45

score 3 · Answer 2 · 2012-02-26T16:20:27.917

3

You need to use dynamic SQL to get this result (the below code assumes SQL Server, I can't speak for other RDBMS').

declare @tableName varchar(100)
declare @query varchar(500)

select @tableName = tablename
from ListOfTables
where id = 0

select @query = 'select * from ' + @tableName

exec (@query)

edited Feb 26 '12 at 16:20

answered Feb 26 '12 at 16:12

You'd need a cursor/etc for that to cycle through the list of table names – OMG Ponies Feb 26 '12 at 16:25
@OMGPonies I didn't think the OP wanted to cycle through all the tables..? – Feb 26 '12 at 16:28

score 0 · Answer 3 · answered Feb 26 '12 at 16:20

Almost the same as @Shark's answer, except you also quote the name of the table to avoid syntax errors.

-- Using variables just for better readability.
DECLARE @Name NVARCHAR(4000)
DECLARE @Query NVARCHAR(4000)

-- Get the relevant data
SET @Name = QUOTENAME(SELECT tablename FROM ListOfTables WHERE id=0)
-- Build query 
SET @Query = 'SELECT * FROM ' + @Schema + '.' + @Name + ''
-- execute it.
EXEC(@Query)

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

3 Answers3