MYSQL query using variable as table name in LEFT JOIN

user_394530 0
Viewed 15396
SELECT var1,var2,var3,table_name 
FROM table1 LEFT JOIN table_name on var3=table_name.id

Meaning I want to dynamically left join table, depending on value of table_name from table1, since var3 is taken from there.

But the above query results in

table table_name does not exist

My mistake of mysql limitation?

left-join mysql
2 Answers

Another way is to unite all tables with a union query:

SELECT *, 44 as table_origin FROM `Table_44`
UNION ALL
SELECT *, 58 as table_origin FROM `Table_58`;

You could even prepare that as view:

CREATE VIEW `AllTheTables` AS
SELECT *, 42 as table_origin FROM `Table_42`
UNION ALL
SELECT *, 44 as table_origin FROM `Table_44`
UNION ALL
SELECT *, 58 as table_origin FROM `Table_58`
UNION ALL
SELECT *, 69 as table_origin FROM `Table_69`;

And thus query it safely:

SELECT * FROM AllTheTables WHERE table_origin IN (44,58) AND something = 'foobar';
-- or --
SELECT * FROM AllTheTables WHERE table_origin = 42 AND something = 'the question';

In your exact case it could look like this:

SELECT var1, var2, var3, table_name 
FROM table1 LEFT JOIN AllTheTables ON table1.var3=AllTheTables.table_origin
user_34233240
Related