-1

I created a database for problem set with 6 column(problemId, a ,b ,c ,d , rightAnswer), where a,b,c,d stores the details of each option and rightAnswer stores the index of right answer such as a, b, c, d. Now I want to extract the index of right answer in rightAnswer (for example, a )and then select a from the table. Here is my code:

SELECT (SELECT rightAnswer FROM table WHERE problemId=1) FROM table WHERE problemId=1

But it didn't work.

KengoTokukawa
  • 55
  • 1
  • 10
  • select requires a column name. You have placed a table result in place of it – Akshey Bhat Sep 30 '16 at 04:33
  • "it didn't work" isn't very descriptive of the behavior you observe. If problemid is unique in the table (or, if the value 1 occurs in problemid on only one row), we would expect the query to return the value stored in the `rightanswer` column. @AksheyBhat: MySQL expects a list of *expressions* following the SELECT keyword. Yes, a column name is an example of an expression, but we can also include more complex expressions, including a scalar subquery (returning one column and at most one row.) – spencer7593 Sep 30 '16 at 04:38
  • it just did not return the content in a column – KengoTokukawa Sep 30 '16 at 04:41
  • Look at this if it can help you [How to select column names dynamically in mySQL](http://stackoverflow.com/questions/15507683/how-to-select-column-names-dynamically-in-mysql) – Bruno Rigoni Sep 30 '16 at 05:44

2 Answers2

1

You could use a CASE expression ... 

 SELECT CASE t.rightanswer 
          WHEN 'a' THEN t.a
          WHEN 'b' THEN t.b
          WHEN 'c' THEN t.c
          WHEN 'd' THEN t.d
          ELSE NULL
        END AS rightanswer_val
   FROM mytable t
  WHERE t.problemid = 1

That would serve to "decode" the value stored in the rightanswer column, and return the value stored in the corresponding column.

If you have some unfathomable need to "embed a select in a select" (as the title of your question suggests), it's possible to do that, though that's not needed for solving this problem.

In SQL, references to identifiers must appear in the text of a SQL statement. Values for identifiers (column names, table names, function names, etc.) cannot be generated dynamically by the execution of the statement. If you want to return the value from a column named col, then the name of that column col must appear in the actual SQL text.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
0

You can use dynamic query for this.

SET @col_name:= (SELECT rightAnswer FROM `table` WHERE problemId=1);
SET @sql:=CONCAT_WS(' ','SELECT ',@col_name,' FROM `table` WHERE problemId=1');
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20