Join T2 to T1 on T2.x = T1.x if there is a match, else on T2.y = T1.y if no match

I have 2 tables: An Events table which contains the teams and the users on those teams who are registered for an event, and a Teams table which contains all teams and their players (independent of Events).

I would like to join Teams onto Events where Teams.user_id = Events.user_id, and then to join any remaining rows in Teams where Teams.team_id = Events.team_id.

So for example, these 2 tables would yield the following result

+---------------------------------------+ +-------------------------+
| Events | | Teams |
+----------+---------+---------+--------+ +---------+---------+-----+
| event_id | team_id | user_id | foo | | team_id | user_id | bar |
+----------+---------+---------+--------+ +---------+---------+-----+
| 1 | 1 | 1 | 1 | | 1 | 1 | A |
+----------+---------+---------+--------+ +---------+---------+-----+
| 1 | 2 | NULL | NULL | | 1 | 2 | A |
+----------+---------+---------+--------+ +---------+---------+-----+
| 1 | 3 | 5 | 0 | | 2 | 3 | A |
+----------+---------+---------+--------+ +---------+---------+-----+
| 2 | 4 | B |
+---------+---------+-----+
| 3 | 5 | A |
+---------+---------+-----+
| 4 | 6 | A |
+---------+---------+-----+


+---------------------------------------------+
| Expected Result |
+----------+---------+---------+--------+-----+
| event_id | team_id | user_id | foo | bar |
+----------+---------+---------+--------+-----+
| 1 | 1 | 1 | 1 | A |
+----------+---------+---------+--------+-----+
| 1 | 1 | 2 | NULL | A |
+----------+---------+---------+--------+-----+
| 1 | 2 | 3 | NULL | A |
+----------+---------+---------+--------+-----+
| 1 | 2 | 4 | NULL | B |
+----------+---------+---------+--------+-----+
| 1 | 3 | 5 | 0 | A |
+----------+---------+---------+--------+-----+


I have tried various queries like the following, but most result in the whole roster from a team in Teams being joined onto each user_id in Events where the team_ids match.

SELECT Events.*, Teams.*
FROM Events
RIGHT JOIN Teams ON CASE
WHEN Teams.user_id = Events.user_id
THEN Teams.user_id = Events.user_id
ELSE Teams.team_id = Events.team_id
END
WHERE Events.event_id = (/* subquery that yields event_id */)

/* or for join */

RIGHT JOIN Teams ON
(Teams.user_id = Events.user_id AND Teams.team_id = Events.team_id)
OR Teams.team_id = Events.team_id
/* also */
RIGHT JOIN Teams ON
(Events.user_id IS NOT NULL AND Teams.user_id = Events.user_id)
OR (Events.user_id IS NULL AND Teams.team_id = Events.team_id)`

Continue reading...