Select using dynamically generated tablename

问题:

I've got the following problem. My table (geo_table) structure is as follows:

foreign_table_id | foreign_table_name | some_other_fields...

foreign_table_name stores names of different tables and foreign_table_id stores ids within these tables. What I want to do is check whether a row in the above mentioned table stores valid reference to some other table. Thus I need to select a row from foreign table which name is based on string stored in foreign_table_name. My code didn't work because it has syntax error but it clearly presents what I wanted to achieve:

SELECT foreign_table_id, foreign_table_name FROM `geo_table` gt
WHERE (
    SELECT COUNT(*) FROM gt.foreign_table_name AS t
    WHERE gt.foreign_table_id = t.uid
) > 0

Any help would be much appreciated

回答1:

If you know all possible table names then you can implement it using conditional syntax:

SELECT foreign_table_id, foreign_table_name FROM `geo_table` gt
WHERE 
    CASE gt.foreign_table_name
        WHEN 'table1' THEN 
            EXISTS (
                SELECT * FROM table1
                WHERE id = gt.foreign_table_id
            )
        WHEN 'table2' THEN 
            EXISTS (
                SELECT * FROM table2
                WHERE id = gt.foreign_table_id
            )
        ELSE
            FALSE
    END

回答2:

This should be done dynamically :

declare @tablename varchar(50)
set @tablename = 'test'
declare @sql varchar(500)
set @sql = 'select * from ' + @tablename
exec @sql