string as mysql table name

Question

The query it self will explain where i'm stuck at, i'm unable to change the schema, is there any way to use the table name that i get from the if statement and continue to query ?

SELECT *,IF(user.type = 1,"staff","admin") as "table" FROM user WHERE "table".user_id = user.id

http://sqlfiddle.com/#!9/842be4/1

I tried setting a variable, still didn't work out. any ideas ? Thank you.

Answer 1

You want to have a dynamic query like

SET @table_name = (SELECT IF(user.type = 1,"staff","admin") FROM `user`);

SET @t1 =CONCAT("SELECT * FROM menu LEFT JOIN ", @table_name," ON ", @table_name,".user_id = user.id");
 PREPARE stmt FROM @t1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;