Hirad Roshandel Hirad Roshandel - 5 months ago 9
SQL Question

Show count value 0 when row doesn't exist MYSQL

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:

SELECT g.*,
Count(*) AS participants
FROM game g,
bet b
WHERE b.game_id = g.id
AND USER = ?
GROUP BY g.id


So to make it more clear lets say I have 3 rows in my Game table. There are 4 bets for game1 , 2 bets for game2 and there is no row associated with game3 in Bet table. The result should be:

g1 4
g2 2
g3 0


I'd appreciated any help.

Answer

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
Comments