CODEWITHSUNDEEP
mysqlsql
bro_php
bro_php

Reputation: 90

string as mysql table name

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.

Upvotes: 3

Views: 3318

Answers (2)

mseifert
mseifert

Reputation: 5670

I believe this will give you what you want.

SELECT * FROM user INNER JOIN staff on user.id = staff.user_id UNION

SELECT * FROM user INNER JOIN admin on user.id = admin.user_id

Upvotes: 1

Rahul
Rahul

Reputation: 77876

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;

Upvotes: 6

Related Questions