CODEWITHSUNDEEP
mysqlsql
Fowler
Fowler

Reputation: 524

Select column based on column name stored in another table

I am trying to get a cell value from another table (UserTable) for which the column of the cell is specified in the original table (DataTable). The cell value is going to be used to calculate a score which will then determine the ordering of the query results.

UserTable:
+--------+------+------+------+
| userid | c1   | c2   | c3   |
+--------+------+------+------+
|    id1 |    0 |    1 |    1 |
|    id2 |    0 |    0 |    1 |
|    id3 |    1 |    0 |    0 |
|    id4 |    1 |    1 |    0 |
+--------+------+------+------+

DataTable:
+--------+------+------+------+
| id     | ColA | ColB | ColC |
+--------+------+------+------+
|      1 | A    | B    | c1   |
|      2 | D    | E    | c3   |
|      3 | G    | H    | c2   |
|      4 | J    | K    | c3   |
+--------+------+------+------+

My current, not working, query is:

SELECT *, 
       (SELECT DataTable.ColC 
        FROM UserTable 
        WHERE UserTable.userid = 'some_user_id') AS score 
FROM DataTable 
ORDER BY score DESC

But this doesn't select the appropriate column in UserTable and instead returns the string value contained in ColC.

The expected output: If 'some_user_id' was set to 'id2' for example. The the expected output would be:

+--------+------+------+------+------+
| id     | ColA | ColB | ColC | score|
+--------+------+------+------+------+
|      2 | D    | E    | c3   |  1   |
|      4 | J    | K    | c3   |  1   |
|      1 | A    | B    | c1   |  0   |
|      3 | G    | H    | c2   |  0   |
+--------+------+------+------+------+

where the score column is simply the lookup of the value from the UserTable.

Is it possible to solve this problem?

Upvotes: 0

Views: 109

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I like to express this by unpivoting the user table:

select dt.*, uts.score
from datatable dt left join
     ((select ut.userid, 'c1' as which, c1 as score
       from usertable ut
      ) union all
      (select ut.userid, 'c2' as which, c2 as score
       from usertable ut
      ) union all
      (select ut.userid, 'c3' as which, c3 as score
       from usertable ut
      ) 
     ) uts
     on dt.colc = uts.which 
where uts.userid = ?;

The reason I prefer this approach is because the subquery suggests the table that you really need. You should not be storing the different score values in columns. You should have a userScores table with one row per user and per score.

Upvotes: 0

Cosmin Gheorghita
Cosmin Gheorghita

Reputation: 1

Declare @expr nvarchar(max);  
Declare @value nvarchar(max);   
DECLARE db_cursor CURSOR  
FOR Select colC from dbo.DataTable;

OPEN db_cursor;  
FETCH NEXT FROM db_cursor INTO @value;  
Set @expr = 'Select ' + @value;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       Set @expr += ',' + @value  
       FETCH NEXT FROM db_cursor INTO @value;  
END;  
CLOSE db_cursor;  
DEALLOCATE db_cursor;  

EXEC (@expr + ' from dbo.UserTable')

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

You cannot do this dynamically. But you can build a CASE statement that maps column names to columns:

SELECT DataTable.*, (
    SELECT CASE DataTable.ColC
        WHEN 'c1' THEN UserTable.c1
        WHEN 'c2' THEN UserTable.c3
        WHEN 'c3' THEN UserTable.c3
    END
    FROM UserTable
    WHERE UserTable.userid = 'id2'
) AS score
FROM DataTable
ORDER BY score DESC

Upvotes: 1

Related Questions