CODEWITHSUNDEEP
mysqljoinsql-order-by
professor.luther.ratigan
professor.luther.ratigan

Reputation: 7

Why are these JOIN statements affecting my order clause in different ways?

I'm familiarizing myself with SQL on sqlzoo, and the final question (#13) was giving issues until I successfully guessed the solution. I initially suspected I had written my ORDER BY clause wrong or that improper data type was to blame, but then I looked up a solution provided by mjsqu here. I only changed the JOIN statement and removed the ORDER BY clause to match their code. Frankly, I still don't understand why the ORDER BY statement was failing to sort the date in the first place.

See here for data and problem set: https://sqlzoo.net/wiki/The_JOIN_operation

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 mdate, matchid, team1, team2
ORDER BY mdate, matchid, team1, team2

Solution code:

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 LEFT JOIN goal ON matchid = id 
GROUP BY mdate, matchid, team1, team2

Upvotes: 0

Views: 26

Answers (1)

Barmar
Barmar

Reputation: 780994

The difference between the two queries is for games where there are no goals. INNER JOIN won't return any rows for those games. LEFT JOIN will return a row for those games, but all the columns from the goals table will be NULL.

Since you're grouping and ordering by goal.matchid, this will group all those games with no goals together when the other grouping columns are the same. You should use game.id instead of goal.matchid in both GROUP BY and ORDER BY.

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 LEFT JOIN goal ON matchid = id 
GROUP BY mdate, id, team1, team2
ORDER BY mdate, id, team1, team2

Upvotes: 1

Related Questions