[SOLVED] How to use nested SELECT statements in sql?

How to use nested SELECT statements in sql?

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 ?

Solution

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;