title: SQL complicated SELECT query
body: <p>I would like to have a select query, where I get all the games that a specific user played with the points of his and the opponents team.</p>
  <p><a href="https://i.stack.imgur.com/sToSV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sToSV.png" alt="Entity relatioship model of my database" /></a></p>
  <p><sup><i>Entity relatioship model of my database</i></sup></p>
  <p><strong>This is my current query:</strong></p>
  <pre><code>Select playsIn.userId,game.id AS &quot;gameId&quot;, plays.won, team.points from game JOIN plays ON plays.gameId = game.id
   JOIN team ON team.id = plays.teamId JOIN playsIn ON team.id = playsIn.teamId WHERE playsIn.userId = 2 
  </code></pre>
  <p><strong>The current query gives me this result:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">userId</th>
  <th style="text-align: center;">gameId</th>
  <th style="text-align: center;">won</th>
  <th style="text-align: center;">points</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">true</td>
  <td style="text-align: center;">19</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Problem:</strong>
  From this query I only get the points of my team, but I would also like to get the points of the other team. I don't know how to get the points of the opponents.</p>
  <p><strong>The result should look something like this:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">userId</th>
  <th style="text-align: center;">gameId</th>
  <th style="text-align: center;">won</th>
  <th style="text-align: center;">pointsMyTeam</th>
  <th style="text-align: center;">pointsOtherTeam</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">true</td>
  <td style="text-align: center;">19</td>
  <td style="text-align: center;">14</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Note: For a game there are always only 2 teams.</p>
  <p>Thank's for helping out!</p>