SQL complicated SELECT query

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.

^{Entity relatioship model of my database}

This is my current query:

Select playsIn.userId,game.id AS "gameId", 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 

The current query gives me this result:

userId	gameId	won	points
1	1	true	19

Problem: 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.

The result should look something like this:

userId	gameId	won	pointsMyTeam	pointsOtherTeam
1	1	true	19	14

Note: For a game there are always only 2 teams.

Thank’s for helping out!

Answer

It sounds like you need to join to the other plays row for that game, the row that matches the game ID but does not match the team ID:

JOIN plays otherplays ON otherplays.gameId = game.id AND otherplays.teamId <> team.id

Then you can join that to the other team row:

JOIN team otherteam ON otherteam.id = otherplays.teamId

And then you can select otherteam.points. By the way, please consider making points a column of the plays table instead of the team table. By making points a column of team, you limit each team to having only one point value and therefore playing only one game.