Left Join Multiple Tables on one master table-CodePudding

I am referring to sqlzoo joining exercise linked here: https://sqlzoo.net/wiki/The_JOIN_operation

I already solved it using 'CASE WHEN' as requested in the question, but I am just trying to do it in a different way.

below is my code which gave me the right answer in all columns except score2 column, it's showing the same result as score1, I was just wondering what mistake I did in the below code.

These are the given tables

This is the target result

 SELECT 
  MDATE, 
  TEAM1, 
  COUNT(X.MATCHID) AS SCORE1,
  TEAM2,
  COUNT(Y.MATCHID) AS SCORE2 
FROM GAME
LEFT JOIN GOAL X ON ID = X.MATCHID AND TEAM1 = X.TEAMID
LEFT JOIN GOAL Y ON ID = Y.MATCHID AND TEAM2 = Y.TEAMID
GROUP BY MDATE, TEAM1, TEAM2

CodePudding user response：

WITH xGoalQ
AS
(
    SELECT MATCHID, TEAMID , COUNT(1) AS Score
    FROM GOAL
    GROUP BY MATCHID, TEAMID
)

SELECT 
  MDATE, 
  TEAM1, 
  X.Score AS SCORE1,
  TEAM2,
  Y.Score AS SCORE2 
FROM GAME
LEFT JOIN xGoalQ X ON ID = X.MATCHID AND TEAM1 = X.TEAMID
LEFT JOIN xGoalQ Y ON ID = Y.MATCHID AND TEAM2 = Y.TEAMID
ORDER BY MDATE, TEAM1, TEAM2

SELECT 
  MDATE, 
  TEAM1, 
  (SELECT COUNT(1) FROM GOAL WHERE MATCHID = GAME.Id AND TEAMID = GAME.Team1) AS SCORE1,
  TEAM2,
  (SELECT COUNT(1) FROM GOAL WHERE MATCHID = GAME.Id AND TEAMID = GAME.Team2) AS SCORE2 
FROM GAME
ORDER BY MDATE, TEAM1, TEAM2

CodePudding user response：

You are misunderstanding how COUNT works.

It counts all non-null values passed to it. And it does not count the number of MATCHID in each table before the join, it only works after the join.

So the final resultset after joining is then fed into the GROUP BY, and only then is COUNT executed. Since MATCHID is non-null, the same result comes out for both counts.

Instead you need to pre-group, either using LEFT JOIN or APPLY

SELECT 
  GAME.MDATE, 
  GAME.TEAM1, 
  G1.SCORE1,
  GAME.TEAM2,
  G2.SCORE2 
FROM GAME
LEFT JOIN (
    SELECT MATCHID, TEAMID, COUNT(*) SCORE1
    FROM GOAL
    GROUP BY MATCHID, TEAMID
) G1 ON GAME.ID = G1.MATCHID AND GAME.TEAM1 = G1.TEAMID
LEFT JOIN (
    SELECT MATCHID, TEAMID, COUNT(*) SCORE2
    FROM GOAL
    GROUP BY MATCHID, TEAMID
) G2 ON GAME.ID = G2.MATCHID AND GAME.TEAM2 = G2.TEAMID;

Alternately

SELECT 
  GAME.MDATE, 
  GAME.TEAM1, 
  G1.SCORE1,
  GAME.TEAM2,
  G2.SCORE2 
FROM GAME
OUTER APPLY (
    SELECT COUNT(*) SCORE1
    FROM GOAL G1
    WHERE GAME.ID = G1.MATCHID AND GAME.TEAM1 = G1.TEAMID
) G1
OUTER APPLY (
    SELECT COUNT(*) SCORE2
    FROM GOAL G2
    WHERE GAME.ID = G2.MATCHID AND GAME.TEAM2 = G2.TEAMID
) G2;