Single MySQL query on three tables
<p>I have three tables as below:</p>

<p>Table 1: <strong>player_game</strong></p>

<pre><code>player_id | game_id
-------------------
    1     |    1
    2     |    2  
    5     |   20
    4     |    1
    2     |   20
-------------------
</code></pre>

<p>Table 2: <strong>players</strong></p>

<pre><code>player_id | player_name | player_age
------------------------------------
    1     |   John      |  20
    2     |   Abe       |  18
    5     |   Lisa      |  21
    4     |   Mary      |  25
    3     |   Romeo     |  21
------------------------------------
</code></pre>

<p>Table 3: <strong>games</strong></p>

<pre><code>game_id   | game_name    | diff_level
------------------------------------
    1     | AOE          |  easy
    2     | Contra       |  easy
   11     | Tribalwars   |  difficult
   20     | Tanks        |  novice
   25     | Minesweeper  |  medium
------------------------------------
</code></pre>

<p>I want to write a query that looks up the <code>players</code> and <code>games</code> table by using the IDs on the <code>player_game</code> table. The resulting table should give the player name and the corresponding games name. All the names of the players should be returned. If any player does not have an ID in the <code>player_game</code> table, it should list a <code>NULL</code>. Expected output is below:</p>

<p>Result:</p>

<pre><code>Player Name |   Games Name
--------------------------
  John      |   AOE
  Abe       |   Contra
  Lisa      |   Tanks
  Mary      |   AOE
  Romeo     |   NULL
  Abe       |   Tanks
--------------------------
</code></pre>

<p>I have written the following query but its not working:</p>

<pre><code>SELECT p.player_name AS "Player Name", g.game_name AS "Games Name"
FROM players p
LEFT OUTER JOIN player_game pg
ON p.player_id = pg.player_id
JOIN games g
ON g.game_id = pg.game_id;
</code></pre>

<p>Please advise what am I doing wrong.</p>