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

Question

The question states "List every match with the goals scored by each team as shown below."

This is the result that the question is asking me to show.

I'm quite confused with LEFT JOIN in particular for this problem. Initially, I used the this 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    
   **JOIN**  goal ON matchid = id GROUP BY mdate, team1, team2

However, this does not give the right answer, as the SQLZOO result is not correct. So, I looked up on the Internet for the answer, and it states this:

   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, team1, team2

How did they know which kind of JOIN to use? I know that for the LEFT JOIN it takes all information from the game table and merges to the goal which includes only the matching information to the goal table. The JOIN table will only include information that both tables have in common.

The Database tables

I'm surprised you're asking about the join rather than the conditional aggregation `CASE` expressions. `LEFT JOIN` just means keep all records on the left side of the join, even if a record does not match. Think of it as an `INNER JOIN` on steroids. — Tim Biegeleisen, Jul 06 '17 at 05:28
@TimBiegeleisen But if you were doing this problem, would you use the left join on your first thought?　I still cant really tell ...haha.. Do you have any idea? — user234568, Jul 06 '17 at 05:38
Yes, I would, if I thought that some games might not have any goals. Do you see it? An inner join would remove all games not having any goals. Unlikely, but it could happen. — Tim Biegeleisen, Jul 06 '17 at 05:40
I would highly suggest you to increase readability of the code and prefix all column names with the proper table names. Right now anyone reading the query will have to either look up the columnnames by table, or simply guess where the columns are coming from. So change `ON matchid = id` to `goal.matchid = game.id`, etc. — SchmitzIT, Jul 06 '17 at 07:12
Please don't use links or images, put code & tables inline in code format. If you also want to add a diagram, OK, but give DDL. Also: "The result" for what input? What does "not show correct" mean? Give input and output. What does a row in each table (base or query) say in terms of its columns? [mcve] — philipxy, Jul 09 '17 at 09:34
@TimBiegeleisen Use of vague phrases like "an INNER JOIN on steroids" is exactly what impedes the OP's reasoning/understanding. — philipxy, Jul 28 '17 at 02:28

philipxy · Answer 1 · 2017-07-28T02:11:14.973

how did they know which kind of JOIN to use

The definition of LEFT JOIN ON is that it returns the rows of (INNER) JOIN ON plus the unmatched rows of the left table extended by NULLs.

(Where a left row is "unmatched" in an INNER JOIN ON when it is not used to form a result row.)

Thus, there is always at least one row output for every left row input. There will more than one when the the associated INNER JOIN outputs more than one row for some left row(s) input. If the LEFT JOIN is ON a condition requiring that a left FK (foreign key) subrow equals its referenced right table subrow then there will be exactly one row output for each row input.

for the LEFT JOIN it takes every information from game table and merge to the goal which includes only the matching information to the goal table. The JOIN table will only include information that both tables have in common.

The terms you are using are just too vague. They don't actually describe what the operators calculate. (In this case or the general case.) "takes information from", "merge to", "includes only the matching information to" and "include information that both tables have in common" might evoke what the operators do if you already know, but they don't clearly describe or define. It is important in technical work to memorize the exact definitions of technical terms and to be able use only those terms only in the right way.

Is there any rule of thumb to construct SQL query from a human-readable description?

Use of vague phrases like "Understanding is far more important than memorization" is exactly what impedes the OP's reasoning/understanding. — philipxy, Jul 29 '17 at 07:16

score 0 · Answer 2 · edited Aug 06 '21 at 09:23

Here we are using left join instead of inner join, for the simple fact that ques asks to list all the matches, and it is not necessary that in every match a goal is scored, we can have missing values of matchid in goal column, which you can check by executing the following query:

select * from game left join goal on id=matchid

where matchid is NULL

and you will find that for matchid 1028 and 1029 no goal was scored.

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

2 Answers2