Selecting using two column names, using the other one if one is known of each record

Question

I'm not entirely sure how to phrase this as a concise question title.

I have a games table which contains of records representing games of a player against another. The relevant columns of this table are userid1 and userid2. I have another table called accounts, consisting of the columns id and username.

I'd like to fetch the username of the opponent from the accounts table. The opponent is the user of which the id is not equal to the known user id. So, if userid1 = 123 then I'd like to fetch accounts.username of the user where accounts.id is equal to userid2 of the same games record. The same goes the other way round if userid2 = 123.

I've so far only been able to select the opponent's username separately using two queries:

SELECT * FROM games, accounts
WHERE games.userid1 = 123 AND accounts.id = games.userid2

and:

SELECT * FROM games, accounts
WHERE games.userid2 = 123 AND accounts.id = games.userid1
(swapped:         ^                                     ^)

However, I'd like to combine these two queries nicely into one. So, is it possible to get accounts.username where accounts.id is equal to:

• games.userid2 if games.userid1 is equal to the known user id
• games.userid1 if games.userid2 is equal to the known user id

and if so, how?

Answer 1

You can use a case statement in your join condition, something like this:

SELECT * FROM games g
    JOIN accounts a 
      ON a.id = case g.userid1 when ? then g.userid2 else g.userid1 end
WHERE 
    g.userid1 = ? OR g.userid2 = ?

However, depending on your indexes, it may be quicker to use a union, eg.

  SELECT * FROM games g
      JOIN accounts a ON a.id = case g.userid2
  WHERE g.userid1 = ?
UNION ALL
  SELECT * FROM games g
      JOIN accounts a ON a.id = case g.userid1
  WHERE g.userid2 = ?

An alternative query using OR,

SELECT * FROM games g, accounts a 
WHERE 
      (g.userid1 = ? AND g.userid2 = a.id) 
   OR (g.userid2 = ? AND g.userid1 = a.id)