MySQL select query with variable column name

Question

I am looking for a simplest solution to query one table based on results from another query.

Table 1:
id    rslt  
-----------
1      r1  
2      r2   
3      r3 

Table 2:
  r1  r2  r3  
 ------------------
  a1  b1  c1
  a2  b2  c2
  a2  b3  c3

query: `SELECT rslt FROM table1 WHERE id = 1` will return r1

I want to be able to use this dynamically to query data from table2 i.e.

SELECT r1 FROM table2

I tried:

select (SELECT rslt from table1 where id = 1) from table2

That is returning 'r1' instead of values in r1 column values (a1, a2, a3)

Thanks in advance

Solution 1

The simplest solution is to use two separate queries.

We use the result from the first query to dynamically generate the SQL text for the second query.

mysql> SET @colname := '' ; 
mysql> SELECT t.rslt FROM table1 t WHERE t.id = 1 ORDER BY t.rslt LIMIT 1 INTO @colname ;

mysql> SET @sql := CONCAT('SELECT `',@colname,'` FROM table2 ORDER BY 1') ;
mysql> PREPARE stmt FROM @sql ; 
mysql> EXECUTE stmt ;

mysql> DEALLOCATE PREPARE stmt ;

N.B. including @colname as part of the SQL text, dynamically prepared SQL, is a potential SQL Injection vulnerability.

---

If the requirement is to get something similar done in the context of a single SQL statement, then the statement needs to anticipate the possible values to be returned for the query from table1, and include explicit references to the possible columns from table2. For example, something like this:

  SELECT CASE ( SELECT t.rslt FROM table1 t WHERE t.id = 1 LIMIT 1 )
           WHEN 'r1' THEN r.r1 
           WHEN 'r2' THEN r.r2 
           WHEN 'r3' THEN r.r3 
           ELSE NULL
         END AS c2
    FROM table2 r
   ORDER BY ...

This isn't necessarily the most efficient way to write the query, but it demonstrates the pattern.

---

Within a SQL statement, the identifiers (table names, column names, function names) must be specified explicitly; these cannot be derived dynamically at run time. This is because of how SQL statements are processed... parsing the SQL text for syntax, then parsing for semantics (valid references and privileges), evaluating relative cost for available access paths, selecting an execution plan, and then executing that plan.

That is, the behavior observed with this SQL is what we expect:

 SELECT (SELECT rslt FROM table1 WHERE id = 1) FROM table2

The SQL text is prepared, and at execution time, for each row in table2, the subquery in the SELECT list is executed. If the subquery returns a scalar value, the scalar value is returned as a column of the outer query. The value returned by the subquery is a value, it is not (and cannot be) evaluated as a column name.

Solution 2

You could use MySQL query concat to do that.

In this case, you must use SELECT INTO as follow:

SELECT rslt INTO @columnname FROM table1 WHERE id = '_id';
SET @sql_text = concat('SELECT ', @columnname, ' FROM table2;');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

As you can see, you first need to save your column name into some variable, such as @columname, and then you will be able to use it on future queries. It's a best practice to declare variables before its use, you can do that by using declare statement.

Solution 3

You can do:

with x as (
  select rslt from table1 where id = 1
)
select
  case when x.rslt = 'r1' then t2.r1
       when x.rslt = 'r2' then t2.r2
       when x.rslt = 'r3' then t2.r3
  end
from table2 t2
cross join x 

Comments

[wchiquito]

Try 13.5 Prepared SQL Statement Syntax.

[goryef]

Thanks. I know that way. But my actual table one has about 50 values. I was hoping for a more generic solution.

[The Impaler]

I don't see any other way in MySQL. If you were using PostgreSQL you could use "crosstabs"; if you were using SQL Server you could try pivoting the table.