How to use nested SELECT statements in sql?

Question

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 ?

I want to display my second table without specify its name directly, means its name should be came from table 1, by using nested SELECT statement, but it didn't worked — Siraj Alam, Jun 21 '16 at 12:23

score 4 · Accepted Answer · answered Jun 21 '16 at 12:29

4

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;

answered Jun 21 '16 at 12:29

Giorgos Betsos

71,379
9
63
98

Thanks Betsos, it worked well, but still I want to Know some things just for knowledge,is this the character variable `@s` ?, which you have store the query. And are these statements are the part of dynamic sql or what ? – Siraj Alam Jun 21 '16 at 12:40

How to use nested SELECT statements in sql?

1 Answers1