Siraj Siraj - 5 months ago 14
MySQL Question

How to displaying table by specifying its name from another table?

I have two tables as

Book

+------+--------------+--------+----------+
| bkey | bname | nochap | b_id |
+------+--------------+--------+----------+
| 1 | Let Us C | 17 | luc13 |
| 2 | OOP with C++ | 17 | oopwcpp6 |
+------+--------------+--------+----------+


and

luc13

+------+-----------------+---------+
| cnum | cname | c_id |
+------+-----------------+---------+
| 1 | Getting Started | luc13gs |
| 2 | C Instructions | luc13ci |
+------+-----------------+---------+


so, as you can see that the table name of second table (luc13) is the element of first table book. So while displaying the second table, i don't want to specify it's name, so I write the query

SELECT * FROM (SELECT b_id FROM book WHERE bkey = 1)


and its shows error,

ERROR 1248 (42000): Every derived table must have its own alias

So I searched it and write the query again

SELECT * FROM (SELECT b_id FROM book WHERE bkey = 1 AS custom)


Now the error is removed but still the output is

+-------+
| b_id |
+-------+
| luc13 |
+-------+


So I think only
SELECT b_id FROM book WHERE bkey = 1
is executed.
So is there any way to display my table luc13 without directly specifying its name ?

Thanks in advance :)

Answer

You have to use a prepared statement for this:

SET @s := CONCAT('SELECT * FROM ', (SELECT b_id FROM book WHERE bkey = 1));

PREPARE stmt FROM @s;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;