Why does my SQL query return no rows, when sum is 0

Question

I'm trying to solve problem 13 from here: http://sqlzoo.net/wiki/The_JOIN_operation In it I'm supposed to "List every match with the goals scored by each team as shown". I thought this would be the answer:

SELECT 
    mdate, team1,
    SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) score1,
    team2,
    SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM 
    game 
JOIN 
    goal ON matchid = id
GROUP BY 
    team1, team2, mdate
ORDER BY 
    mdate

However, this query returns no rows if both teams score is 0. I tried wrapping the SUM in the ISNULL and COALESCE, but it doesn't help.

To tell you the truth I'm not really looking for an answer on how to solve the problem, but rather an answer explaining why is this query not showing me the rows when the score is 0:0. Also if someone could help me find the meaning behind 0 column_name or 1 column_name (as it seems to be the result of above CASE), it seems to be 0*column_name or 1*column_name, but I'm not certain, and google is filled with unrelated results when I try to search it for answers.

PS: Yes, I did read the first 20 suggested stack overflow questions with the same or similar names, I'm sorry if it's a duplicate

Stefano Zanini · Accepted Answer · 2017-05-05 15:10:24Z

You're not getting any result for 0-0 games because the join operator returns you only those rows that are matched across the two tables. Another way to say that is that you'll only get rows where the ID you're specifiying in your ON clause (the match ID in your case) is available in both tables.

A 0-0 game won't have any record in the goal table, that's why the inner join you're using is not returning anything for it.

You need to use a left join, so that all rows of the left table (game in your case) are preserved, even if they've got no matching row in the right table (goal).

SELECT  t1.mdate,
        t1.team1,
        SUM(CASE WHEN t2.teamid = t1.team1 THEN 1 ELSE 0 END) score1,
        t1.team2,
        SUM(CASE WHEN t2.teamid = t1.team2 THEN 1 ELSE 0 END) score2
FROM    game t1
LEFT JOIN 
        goal t2
ON      t2.matchid = t1.id
GROUP BY t1.team1, t1.team2, t1.mdate

I also recommend you to use aliases for the tables and to prefix them to the column names, to avoid confusion about which field belongs to which table.

Collectives™ on Stack Overflow

Why does my SQL query return no rows, when sum is 0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related