Q:

Use LEFT OUTER JOIN to return count even if 0

I am trying to return all of the specified event_abr's even if the count is 0.
Here is my SQL Query:
SELECT
    basketball_event_types.event_abr,
    count(*) value
FROM
    basketball_event_types
    LEFT OUTER JOIN basketball_game_events on basketball_event_types.id = basketball_game_events.event_type_id
    LEFT JOIN users on basketball_game_events.player_id = users.id
WHERE
    users.id = 198
AND
    basketball_game_events.game_id = 213
AND
    (basketball_event_types.event_abr = "TO" OR basketball_event_types.event_abr = "AST")
GROUP BY basketball_event_types.event_abr

However, when I run this, the specified user (198) has 0 AST's for the specified game (213). I want AST -> 0 to pop up, but it just doesn't. The two big things I tried were adding OUTER ot the LEFT JOIN, and I also tried using ifnull(count(*), 0). Neither gave me the result I was looking for.

A:

The filtering needs to be in the on clause.  And you need to fix the count():
SELECT bet.event_abr, COUNT(u.id) as value
FROM basketball_event_types bet LEFT JOIN
     basketball_game_events bge
     ON bet.id = bge.event_type_id AND bge.game_id = 213 LEFT JOIN
     users u 
     ON bge.player_id = u.id AND u.id = 198
WHERE bet.event_abr IN ('TO', 'AST')
GROUP BY bet.event_abr;

Note the changes:

The filtering is moved to the ON clauses -- both filters to the appropriate clauses.
The COUNT() counts the values in the last LEFT JOINed table.
Table aliases make the query easier to write and to read.
IN is simpler than a chain of OR conditions.

