MYSQL query using variable as table name in LEFT JOIN

Question

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?

I don't think you can do this. You will likely need to pass the table name in to a stored procedure if you want this type of functionality or redesign what you're trying to do. — spig
– spig, Commented Sep 5, 2010 at 14:39
It's not a MySQL limitation. More like SQL limitation if you can call it that, but I wuold rather say, it's an indication that something's wrong with your database design. — Mchl
– Mchl, Commented Sep 5, 2010 at 15:01
Main table contains objects. Each object can have attributes. There are separate tables for each attribute listing possible values - values can be very long (several hundred chars) and there are many possible values (thousands). So the main table has 2 fields per attribute: attr_table_name, attr_table_id - to save space and to allow attribute name modification. When query is run, I return actual attribute value, not the id, thats why I need LEFT JOIN and dynamic table naming. How else would you design the DB? — selytch
– selytch, Commented Sep 13, 2010 at 4:11

Damien · Accepted Answer · 2010-09-05 14:44:08Z

14

Table names, as well as column names, can't be dynamic in an SQL query. So you have to apply your logic programmatically, using 2 queries, or with a stored procedure, see an example here: http://forums.mysql.com/read.php?98,126506,126598#msg-126598

answered Sep 5, 2010 at 14:44

Damien

2,2541 gold badge22 silver badges30 bronze badges

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

Comments

luckydonald · Accepted Answer · 2018-07-20 15:02:24Z

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

Collectives™ on Stack Overflow

MYSQL query using variable as table name in LEFT JOIN

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related