SQL derived table results in unknown column

问题:

+------------------+        +------------------+    
| object           |        | example          |
+------------------+        +------------------+    
| id               |        | id               |    
| table_name       |        | ...              |    
| ref_id           |        |                  |    
+------------------+        +------------------+    

object  [ id = 5, table_name = example, ref_id = 2]
example [ id = 2 ]

I have a table which can represent a group of objects originating from a number of different tables. These objects all have an id, referenced by object.ref_id.

I am trying to select the original object (from the table referenced by object.table_name) by object.id. For this, I am trying to use the following SQL query. Though this results in #1054 - Unknown column 'entity.id' in 'where clause'.

SELECT  * 
FROM    (
        SELECT  table_name
        FROM    object 
        WHERE   id = 5) AS entity
WHERE   entity.id = (
        SELECT  ref_id
        FROM    object 
        WHERE   id = 5)

What am I doing wrong?

CASE EXAMPLE

SELECT * 
FROM example 
WHERE id = (
    SELECT  ref_id
    FROM    object 
    WHERE   id = 5 )

Although, for my case the FROM clause is not as easy as in the example, since this value is normally derived from object.table_name.

UPDATE WITH TOMBOM'S APPROACH

Using the solution from Tombom results in an error, for the sake of testing I have replaced the variables in the CONCAT function.

SET @sql = CONCAT('SELECT * FROM ', 'example', ' WHERE id = ', '1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#1243 - Unknown prepared statement handler (stmt) given to EXECUTE

回答1:

You can't tell MySQL that the result of a (sub)query is a table name this way. You'd have to build a dynamic statement with the result.

SELECT @tableName:=table_name, @refId:=ref_id FROM object WHERE id = 5 LIMIT 1;

SET @sql = CONCAT('SELECT * FROM ', @tableName, ' WHERE id = ', @refId);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE: Please have a try with a stored procedure:

DELIMITER $$
CREATE PROCEDURE procName(IN my_id INT)
BEGIN
SELECT @tableName:=table_name, @refId:=ref_id FROM object WHERE id = my_id LIMIT 1;

SET @sql = CONCAT('SELECT * FROM ', @tableName, ' WHERE id = ', @refId);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

Then execute the stored procedure with

CALL procName(5);

回答2:

I've encountered exactly the same problem recently. When I deleted the last line DEALLOCATE PREPARE stmt; everything started working.