how to retrieve table records from tables table?

Question

I want to retrieve table records from tables table here below is what i explained clearly i hope u understand

This is what I have tried but I am not getting the results I expect.

Table 1

----------------------------------------------------------------------------
tables        |    id
----------------------------------------------------------------------------
test               1
pure               2
root               3
link               4
----------------------------------------------------------------------------

Test table

----------------------------------------------------------------------------
id    |    name    |    gender
----------------------------------------------------------------------------
1          jack           m
2          dude           m
3          crop           m
4          pert           m
----------------------------------------------------------------------------

My query to retrieve test table using table

select * from(select tables from table1 where id=1) as info;

The result

----------------------------------------------------------------------------
tables
----------------------------------------------------------------------------
test


----------------------------------------------------------------------------

Expected output

----------------------------------------------------------------------------
id    |    name    |    gender
----------------------------------------------------------------------------
1          jack           m
2          dude           m
3          crop           m
4          pert           m
----------------------------------------------------------------------------

Oracle tag is for oracle db, even though oracle own MySQL. Do not use oracle tag for MySQL queries, thanks — Caius Jard, Dec 09 '18 at 05:33

score 0 · Answer 1 · answered Dec 09 '18 at 05:45

0

SELECT is, name, gender FROM test
    JOIN table1 
    ON test.id = table1.id // MATCH HERE PRIMARY KEY AND FOREIGN KEY
    ORDER BY name;// YOU CAN ALSO ODER BY YOUR TEST.ID

answered Dec 09 '18 at 05:45

Ferrakkem Bhuiyan

2,741
2
22
38

sir,here we don't know about table name like test..we want data of some table which was in table1 whether it may be test,pure ,link..etc for that we use id to retrieve that table from table1.. – seepana avinash Dec 09 '18 at 05:51

score 0 · Answer 2 · edited Dec 09 '18 at 16:50

You can solve it with openquery. Create a stored procedure that run a query as follow:

delimiter //;
create or replace procedure execute_dynamic_sql(tableName  in  varchar(100))
begin
    set @query = 'select * from ?'; 
    set @tableName = tableName; 
    prepare stmt1 from @query;
    execute stmt1 using @tableName;
    deallocate prepare stmt1;

end ;
//

call the stored procedure by any table name argument:

call execute_dynamic_sql('your_table_name') ;

score 0 · Answer 3 · answered Dec 09 '18 at 07:08

0

You would not get data from another table by using subquery. You have to use prepare statement for that.

SET @table = (select tables from table1 where id=1);
set @qry = concat('select * from ',@table);
prepare stmt from @qry;
execute stmt;

answered Dec 09 '18 at 07:08

Dibyendu Ghosh

15
2

how to retrieve table records from tables table?

3 Answers3