title: MySQL Query: get all matches from the matches table along with the name of the team from the teams table given that when …
body: <p>I have the following table structures:</p>
  
  <p>matches:</p>
  
  <pre><code>+-------------------+---------------------------+------+-----+-------------------+-----------------------------+
  | Field             | Type                      | Null | Key | Default           | Extra                       |
  +-------------------+---------------------------+------+-----+-------------------+-----------------------------+
  | id                | bigint(20)                | NO   | PRI | NULL              | auto_increment              |
  | creator_id        | bigint(20)                | NO   |     | NULL              |                             |
  | mode              | enum('versus','freeplay') | NO   |     | NULL              |                             |
  | name              | varchar(100)              | NO   |     | NULL              |                             |
  | team_1_id         | varchar(100)              | NO   |     | NULL              |                             |
  | team_2_id         | varchar(100)              | NO   |     | NULL              |                             |
  +-------------------+---------------------------+------+-----+-------------------+-----------------------------+
  </code></pre>
  
  <p>teams:</p>
  
  <pre><code>+--------------+--------------+------+-----+---------+----------------+
  | Field        | Type         | Null | Key | Default | Extra          |
  +--------------+--------------+------+-----+---------+----------------+
  | id           | bigint(20)   | NO   | PRI | NULL    | auto_increment |
  | creator_id   | bigint(20)   | NO   | MUL | NULL    |                |
  | name         | varchar(100) | NO   |     | NULL    |                |
  +--------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>I need a query where we get all matches from the matches table along with the name of the team from the teams table given that when mode is "versus" the name of the team is taken from the teams table but when the mode is "freeplay" the name of the team is team_1_id or team_2_id themselves (they can hold strings, that is why they are varchar instead of int) without going to the teams table.</p>