Whats wrong with my query with CASE statement

Question

I'm trying to solve #13 on http://www.sqlzoo.net/wiki/The_JOIN_operation

"List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises."

Here's my query:

SELECT game.mdate, game.team1, 
SUM(CASE WHEN goal.teamid = game.team1 THEN 1 ELSE 0 END) AS score1,
game.team2,
SUM(CASE WHEN goal.teamid = game.team2 THEN 1 ELSE 0 END) AS score2

FROM game INNER JOIN goal ON matchid = id
GROUP BY game.id
ORDER BY mdate,matchid,team1,team2

I get the result "Too few rows". I don't understand what part I got wrong.

Answer 1

An INNER JOIN only returns games where there have been goals, i.e. matches between the goal and game table.

What you need is a LEFT JOIN. You need all the rows from your first table, game but they don't need to match all the rows in goal, as per the 0-0 comment I made on your question:

SELECT game.mdate, game.team1, 
SUM(CASE WHEN goal.teamid = game.team1 THEN 1 ELSE 0 END) AS score1,
game.team2,
SUM(CASE WHEN goal.teamid = game.team2 THEN 1 ELSE 0 END) AS score2

FROM game LEFT JOIN goal ON matchid = id
GROUP BY game.id,game.mdate, game.team1, game.team2 
ORDER BY mdate,matchid,team1,team2

This returns the 0-0 result between Portugal and Spain on 27th June, which your initial answer missed out.