title: SQL: How to make multiple joins to the same column of a table without overriding results?
body: <p>I have a table of basketball matches and a table of basketball teams like this:</p>
  
  <pre><code>MATCHES:
  ID   |   HOME_TEAM_ID  |  AWAY_TEAM_ID | SCORE_HOME | SCORE_AWAY
  ----------------------------------------------------------------
  1    |       20        |       21      |     80     |    110
  2    |       12        |       10      |     96     |     90
  
  
  TEAMS:
  ID   |   NAME
  -------------------------
  20   |   BULLS
  21   |   KNICKS
  </code></pre>
  
  <p>Given a match ID, I would like to retrieve both the score and the team names. How do I make a JOIN that retrieves both team names from the teams table?</p>
  
  <p>I have tried:</p>
  
  <pre><code>SELECT *
  FROM matches AS m
  JOIN teams AS t1 ON t.id = m.home_team_id
  JOIN teams AS t2 ON ti.id = m.away_team_id
  WHERE m.id = 1
  </code></pre>
  
  <p>...but here the result from the second JOIN statement seems to override the one from the first, so I only get one name:</p>
  
  <pre><code>[id] =&gt; 1
  [score_home] =&gt; 80
  [score_away] =&gt; 110
  [name] =&gt; KNICKS
  </code></pre>
  
  <p>I have also tried:</p>
  
  <pre><code>SELECT *
  FROM matches AS m
  JOIN teams AS t ON (t.id = m.home_team_id OR t.id = m.away_team_id)
  WHERE m.id = 1
  </code></pre>
  
  <p>...which returns two results :</p>
  
  <pre><code>[id] =&gt; 1
  [score_home] =&gt; 80
  [score_away] =&gt; 110
  [name] =&gt; BULLS
  </code></pre>
  
  <p>and</p>
  
  <pre><code>[id] =&gt; 1
  [score_home] =&gt; 80
  [score_away] =&gt; 110
  [name] =&gt; KNICKS
  </code></pre>
  
  <p>I would like to make a query that returns something like this</p>
  
  <pre><code>[id] =&gt; 1
  [score_home] =&gt; 80
  [score_away] =&gt; 110
  [name_home_team] =&gt; BULLS
  [name_home_team] =&gt; KNICKS
  </code></pre>
  
  <p>Is that possible?</p>