Using subquery value as table name

Question

Here is a problem which is not working for me in the way I expected. This is the query

SELECT  `table_name` 
FROM    `category` 
WHERE   `id` = ( SELECT `category_id` 
                 FROM    `assets` 
                 WHERE   `id` = '24028'  )

which is returning the value photos which I need to use as a table name,

enter image description here

from where I need to retrieve final values. So I have used it like this:

SELECT * 
FROM   ( 
         SELECT  `table_name` 
         FROM    `category` 
         WHERE   `id` =  ( SELECT `category_id` 
                           FROM   `assets` 
                           WHERE `id` = '24028'
                          )
       )

But this returns the error

#1248 - Every derived table must have its own alias

So, have used it like,

SELECT * 
FROM  (
        SELECT `table_name` 
        FROM   `category` 
        WHERE  `id` = (  SELECT `category_id` 
                         FROM   `assets` 
                         WHERE  `id` = '24028' )
       ) as `photos`

But again it is returning the same value as in the previous image. But what I am expecting is it should return the value of:

SELECT * FROM `photos`

where photos is the value returned by the subquery.

How many "table_name" possibilities are there a few, or a LOT of them. — DRapp
– DRapp, Commented May 15, 2013 at 17:23

Seth · Accepted Answer · 2013-05-16 02:59:45Z

4

By the looks of it, you are trying to build a query with a dynamic 'FROM' table name. The only way to do this is to build the SQL in code, or to use a prepared statement:

DELIMITER //; 
SET @s := CONCAT('SELECT * FROM ', (SELECT  `table_name` FROM `category` WHERE `id` = (SELECT `category_id` FROM `assets` WHERE `id` = '24028'));

PREPARE stmt FROM @s;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;
//

edited May 16, 2013 at 2:59

answered May 15, 2013 at 16:07

Seth

1,3731 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stranger Over a year ago

1st query again returning the same value, 2nd query is invalid since i need to use a value from table category to substitute in another query..

Seth Over a year ago

@Udhay in your procedure, right before 'EXECUTE stmt;', you can put: 'SELECT @s;' to see what the concatenated query looks like for debug purposes.

DRapp · Accepted Answer · 2013-05-15 18:11:02Z

0

Then per your comment answer, if you are only ever dealing with one possible "table_name", why not join directly to it

select 
      A.ID,
      A.Category_ID,
      C.Category,
      TP.*
   from
      assets A
         JOIN Category C
            on A.ID = C.ID
         JOIN tbl_photo TP
            on A.ID = TP.ID
   where
      A.ID = '24028'

answered May 15, 2013 at 18:11

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

4 Comments

Stranger Over a year ago

The problem here is that the value tbl_photo need to get again from a query only.. So we cannot substitute it directly. :(

DRapp Over a year ago

@Udhay, I agree with your solution otherwise with dynamic-sql. However, in my question to the OP, they responded there was only one "table" that would be a result, hence tbl_photo and my response to just use it directly.

Stranger Over a year ago

i changed that. Before i meant only 1 possibility all the time.. :)

DRapp Over a year ago

@Udhay, then you will need something like your solution of dynamic-sql

Collectives™ on Stack Overflow

Using subquery value as table name

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related