Return 0 if the condition doesn't match

I have a soccer game where people can predict matches. They can give a match a 1 if they think the home team wins, a 2 if the away club wins and a 3 for a draw. If they predict a match correctly, they get a point. The tables in my database look like this:

Table matches


| id  | home     | away      | result | round_id
| --- | ---------| -------- -| -------|----------|
| 1   | id club 1| id club 2 | 1      | id round 1 
| 2   | id club 5| id club 4 | 3      | id round 1
| 3   | id club 8| id club 5 | 1      | id round 2

Table predictions

| prediction | user_id      | match_id |
| -------- | -------------- | -------- |
| 1        | id user 1      | id match 1 
| 3        | id user 1      | id match 2
| 2        | id user 1      | id match 3

Initially I wanted to do the calculation of the score in PHP, but I think this should also be possible via MySQL only. So I tried something and came up with the following query:

SELECT Count(*) AS points,
       username,
       round_id
FROM   predictions
       LEFT JOIN matches
              ON predictions.match_id = matches.id
       INNER JOIN users
               ON predictions.user_id = users.id
WHERE  predictions.prediction = matches.result
GROUP  BY username,
          round_id
ORDER  BY points DESC,
          username ASC

The query calculates the score per round per user correctly, the only problem is that if a participant has nothing right in a game round, it will not appear in the list at all. Does anyone have any idea what to do to also get a participant in the list if they have 0 points? Translated to the tables mentioned above, round 2 will not return the query because the only match in it was mispredicted. I do want this, however, and so that round 2 is returned with a score of 0.

Result I want:

| points   | username       | round_id|
| -------- | -------------- | --------|
| 2        | John           | 1 
| 0        | John           | 2

Result I have now:

| points   | username       | round_id|
| -------- | -------------- | --------|
| 2        | John           | 1

Solution

The WHERE clause:

WHERE  predictions.prediction = matches.result

filters out any wrong predictions, but even if you remove it, the aggregate function COUNT(*) would count the wrong predictions also.

Join and group like this:

SELECT SUM(p.prediction = m.result) AS points,
       u.username,
       m.round_id
FROM users u 
INNER JOIN predictions p ON p.user_id = u.id
INNER JOIN matches m ON m.id = p.match_id
GROUP BY u.id, u.username, m.round_id
ORDER  BY points DESC, u.username ASC;

The aggregate function SUM() will sum the boolean expressions prediction = result which are evaluated as 1 for true and 0 for false.

See the demo.