title: Join multiple columns from one table to single column from another table
body: <p>I'm trying to learn how to join multiple columns from one table to a single column from another table.</p>
  
  <p><strong>This is my table structure in its simplest form:</strong></p>
  
  <p>teams</p>
  
  <pre><code>id | team_name |
  1  |   teamA   |
  2  |   teamB   |
  3  |   teamC   |
  4  |   teamD   |
  </code></pre>
  
  <p>trades</p>
  
  <pre><code>id |  team_1 (FK to teams.id)  |  team_2 (FK to teams.id)  |
  1  |            1              |              2            |
  2  |            3              |              4            |
  </code></pre>
  
  <p><strong>This is my current SQL which joins trades.team_1 to teams.id:</strong></p>
  
  <pre><code>SELECT teams.team_name AS team1, teams.team_name AS team2, trades.team_1, trades.team_2
  FROM teams
  JOIN trades ON (trades.team_1 = teams.id);
  </code></pre>
  
  <p><strong>My question is, how do I create a second join that also joins trades.team_2 to trades.id?</strong></p>
  
  <p>This would mean both trades.team_1 AND trades.team_2 would be joined to trades.id</p>
  
  <p><strong>The results I want to get back would be:</strong></p>
  
  <pre><code>team1  |  team2  |  team_1  |  team_2  |
  teamA  |  teamB  |    1     |     2    |
  teamC  |  teamD  |    3     |     4    |
  </code></pre>