-1

I have an sql which select value from a table calculated by another sql:

select t.netlist_id from 
(select c from cl2 where pid = 1 order by id limit 1) as t

and I get error message below:

#1054 Unknown column "t.netlist_id" in "field list"

if I change the original sql like this:

select t.* from 
(select c from cl2 where pid = 1 order by id limit 1) as t

I get the result of select c from cl2 where pid = 1 order by id limit 1

Why would this happen and how can I correct my sql?

content of table cl2

|pic|c       |
 ------------
|1  |dcdc    |

content of table dcdc

|netlist_id|
------------
|1         |
Alaya
  • 3,287
  • 4
  • 27
  • 39
  • You have to select netlist_id too in the derived table! – jarlh Mar 08 '15 at 11:34
  • In which table your netlist_id is? Is it in cl2? – Dipen Adroja Mar 08 '15 at 11:39
  • netlist_id is in the derived table t, it is not in cl2 @Dipen_a – Alaya Mar 08 '15 at 11:40
  • 1
    But the only base table involved is cl2. From where comes netlist_id? – jarlh Mar 08 '15 at 11:42
  • 1
    @Alaya : in your alias t table you have only one column c there is no other column apart from this. – Dipen Adroja Mar 08 '15 at 11:42
  • If `dcdc` table is output so `t.netlist_id` should become `t.c as netlist_id`. !!! So Is'nt it? – shA.t Mar 08 '15 at 16:56
  • c is the only column in the derived table – Strawberry Mar 08 '15 at 16:58
  • If it's unclear what is asked in the question: the a look at the link in my answer. The subquery returns a string he wants to use as a table name in another query. The subquery returns 'dcdc' and he wants to see `SELECT * FROM dcdc`. Could be achieved by a programming language but flawed design. – Simo Kivistö Mar 08 '15 at 20:57
  • With thanks to @Simo Kivistö I wrote as answer but in `SQL Server`, just to show a trick, and I hope this will make a correct answer. – shA.t Mar 10 '15 at 17:17

2 Answers2

0

If you use SQL Server I could suggest this answer.
But I wrote this answer, and hope make some answers in mysql.

In SQL Server there is a stored procedure named 

Declare @text varchar(100)

Select @text = 'select * from' + (select top 1 c from cl2 where pid = 1 order by id)

Exec sp_sqlexec @text

This code is very useful as a stored procedure -In SQL Server-.

shA.t
  • 16,580
  • 5
  • 54
  • 111
-1

You cannot fetch a table name from a subquery to the FROM clause of another query. I suggest that you merge all the tables that can appear in the column c of the table cl2 and put them into one table. Put the former table name in a column like this:

table merged_tbl:

|netlist_id|type|
-----------------
|1         |dcdc|

After this you can simply do:

select t.netlist_id from merged_tbl AS t WHERE type IN
(select c from cl2 where pid = 1 order by id limit 1)

There might be another redesign moves you would have to do to your database but those cannot be known just be looking at the example data. Take a look at this question with a similar problem and similar suggestions.

Community
  • 1
  • 1
Simo Kivistö
  • 4,247
  • 3
  • 38
  • 42
  • Kivisto: Alaya is saying that cl2 is not having the netlist_id column... So this can not be the answer. He is missing something. – Dipen Adroja Mar 08 '15 at 11:45
  • I want to `select netlist_id from t`, and t is the result of `select c from cl2 where pid = 1 limit 1` – Alaya Mar 08 '15 at 11:48
  • @Alaya: can you please provide table structure and results for your `select c from cl2 where pid = 1 limit 1' query` – Dipen Adroja Mar 08 '15 at 11:49
  • 1
    @Alaya: As I stated in my answer the only result from your subquery is column c. In case you want to use that under the name netlist_id you can simply rename the column (added that to my answer). If it's something else than renaming the subquery has to be modified... – Simo Kivistö Mar 08 '15 at 11:54
  • @SimoKivistö I have edited the question and add the content of the two table. – Alaya Mar 08 '15 at 15:40
  • Ok, now I get it. Short answer: this cannot be done in normal SQL. What you need to do is: explain what is stored in your tables and we will help you to redesign the database so that you don't have to fetch table names from another tables. Take a look at [this question](http://stackoverflow.com/q/8334493/1288184) – Simo Kivistö Mar 08 '15 at 16:23
  • Added this info to the answer as well. – Simo Kivistö Mar 08 '15 at 16:37