Problems getting LEFT OUTER JOIN to work

Many have answered, but I'll try too and hopefully lend in some more clarification. How I have always interpreted it (and you can check so many other posts I've responded to with LEFT joins), I try to list the table I want everything from first (left side... hence read from left to right). Then left join to the "Other" table (right side) on whatever the criteria is between them... Then, when doing a left join, and there are additional criteria against the right side table, those conditions would stay with that join condition. By bringing them into the "WHERE" clause would imply an INNER JOIN (must always match) which is not what you want... I also try to always show the left table alias.field = right table alias.field to keep the correlation clear... Then, apply the where clause to the basis criteria you want from the first table.. something like

select       a.id,      a.name,      ga.earned_epoch,      ga.offline   from         achievement a          LEFT OUTER JOIN gamer_achievement ga              ON a.id = ga.achievement_id            AND a.game_id = ga.game_id            AND ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024'   where      a.game_id = '1480656849'   order by       convert (a.id, unsigned)

Notice the direct relation between "a" and "ga" by the common ID and game ID values, but then tacked on the specific gamer. The where clause only cares at the outer level of achievement based on the specific game.

In the WHERE clause you discard some rows that the LEFT JOIN would have filled with NULL values. You want to put the condition ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024' inside the JOIN clause.

Another option is:

 LEFT OUTER JOIN (SELECT * FROM gamer_achievement                   WHERE  ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024'                  ) ga 

Remember that the join is performed, and at this time, NULL values come if the condition cannot be met; then the where filter applies.

WHERE clauses filter results from the entire result set. If you want to apply a filter only to the JOIN, then you can add the expression to the ON clause.

In the following query, I've moved the filter expression that applies to the joined table (ga.gamer_id =) from the WHERE clause to the ON clause. This prevents the expression from filtering out rows where gamer_achievement values are NULL.

SELECT a.id,       a.name,       ga.earned_epoch,       ga.offlineFROM   achievement a        LEFT OUTER JOIN gamer_achievement ga        ON ga.achievement_id = a.id       AND ga.game_id = a.game_id       AND ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024'WHERE       a.game_id = '1480656849'ORDER BY CONVERT(a.id, UNSIGNED)