SQL selecting column depending on the value of another column

Question

Consider the following table.

-----------------------------------------------------
| game_id | blue_player_id | red_player_id | turn_of |
-----------------------------------------------------
|    1    |        4       |        5      |  "blue" |
-----------------------------------------------------
|    2    |        1       |        2      |  "red"  |
-----------------------------------------------------
|    3    |        3       |        4      |  "blue" |
-----------------------------------------------------

I want to write a query that selects either the blue_player_id or the red_player_id depending on the turn_of column for a given game id.

Thus for game_id=1 it should return 4, for game_id=2 it should return 2, and for game_id=3 it should return 3.

I know I could do this in two query by first selecting who's turn it is and then selecting the right column in the second query. However, is there a one query method which does not use database specific SQL?

Answer 1

You can use CASE statement if SQL.

SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
   WHEN <othervalue> THEN <returnthis>
   ELSE <returndefaultcase>
   END as something
FROM <table>

You need something like this

SELECT CASE WHEN turn_of='blue'
then blue_player_id else red_player_id 
end as player_id 
FROM Your_TABLE 
WHERE game_id = 0

Answer 2

select gameId,
       case when turn_of = 'blue' 
       then blue_player_id 
       else red_player_id 
       end as PlayerID
from MYTable