Nested Select Query In MySql

Question

Hi I have two tables in my database. suppose table 1 has a name of login and table two has user_info

Table Structure

login

uid : integer

user_name : varchar

password : varchar

tbl_name : varchar

user_info

id : int

name : varchar

.....

suppose in login table for uid =1 tble_name is user_info

then how can I get contens of user_info table from one query ?

SELECT * FROM ( SELECT login.tbl_name  FROM db.login  WHERE uid = 1 )as a

but this returns "user_info" is there any way to do this instead of writing two queries ?

Why do you store the name of a table at all? This seems like a bad design. Couldn't the query simply be written as select * from login join user_info on login.uid = user_info.id? Or maybe I'm missing something vital. — jpw
– jpw, Commented Feb 5, 2015 at 11:46
@jpw one application came form modification and I have to do this, no other way. — droidev
– droidev, Commented Feb 5, 2015 at 11:51

jpw · Accepted Answer · 2015-02-05 12:23:22Z

1

I think the only way to do this is using a dynamic statement, so perhaps using a prepared statement could work for you:

SET @id = 1;
SELECT @table := tbl_name from login where uid = @id;
SET @s = CONCAT('SELECT * FROM ', @table, ' WHERE id = ', @id);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This would return the value for id=1 in the table stored in the tbl_name column in the login table.

answered Feb 5, 2015 at 12:23

jpw

45k6 gold badges71 silver badges90 bronze badges

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

Collectives™ on Stack Overflow

Nested Select Query In MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related