I have 2 tables Game and Bet. I want to count the number of bets for each game. However my current query doesn't include a game when there is no bet for that specific game id.
here is my current query:
Count(*) AS participants
FROM game g,
WHERE b.game_id = g.id
AND USER = ?
GROUP BY g.id
Use a left join and count a column that exists in the bet table. For game records that fail to join to the bet table, a single row will be returned in the pre-grouped result-set that will have null in all columns of the bet table. Because the
count() aggregate function does not count null values, you will get a zero for game records which fail to join.
select g.*, count(b.game_id) participants from game g left join bet b on b.game_id=g.id where user=? group by g.id