I have three tables, and I join them and use where Group by - count, I could not get the countries with zero results in the output. I am still lost.

Here is the SQLfiddle

http://sqlfiddle.com/#!4/e330ec/7

CURRENT OUTPUT

(UKD)   3
(EUR)   2
(USA)   2
(CHE)   1 

EXPECTED OUTPUT

(UKD)   3
(EUR)   2
(IND)   0
(LAO)   0
(USA)   2
(CHE)   1  

CodePudding user response：

You can prefer applying the following steps as an option :

• convert second LEFT JOIN to RIGHT JOIN(since desired missing abbreviations are in COUNTRY_TABLE which stays at right)
• make the filtering condition(followed by the WHERE clause) G.GAME_TYPE = 'GOLF' a match condition by taking next to the ON clause

such as

SELECT C.COUNTRY_CODE, COUNT(GAME_TYPE)
  FROM PLAYER_TABLE P
  LEFT JOIN PLAYER_GAME_TYPE G
    ON P.PLAYER_ID = G.PLAYER_ID
  RIGHT JOIN COUNTRY_TABLE C
    ON P.COUNTRY_ID = C.COUNTRY_ID
   AND G.GAME_TYPE = 'GOLF'
 GROUP BY C.COUNTRY_CODE;

Demo

CodePudding user response：

You can use a RIGHT JOIN as suggested in another answer or you can reorder your joins and use a LEFT JOIN:

SELECT
  C.COUNTRY_CODE,
  COUNT(GAME_TYPE)
FROM
  COUNTRY_TABLE C
  LEFT JOIN PLAYER_TABLE P ON P.COUNTRY_ID = C.COUNTRY_ID
  LEFT JOIN PLAYER_GAME_TYPE G ON P.PLAYER_ID = G.PLAYER_ID
WHERE
  G.GAME_TYPE = 'GOLF'
  OR G.GAME_TYPE IS NULL
GROUP BY
  C.COUNTRY_CODE;

Note the inclusion of OR G.GAME_TYPE IS NULL in the WHERE clause -- if you only have G.GAME_TYPE = 'GOLF', then desired results will be filtered out after the joins.

CodePudding user response：

The simple change of tables join order can solve the problem

SELECT C.COUNTRY_CODE, COUNT(GAME_TYPE)
FROM COUNTRY_TABLE C -- get all countries
LEFT JOIN PLAYER_TABLE P ON P.COUNTRY_ID = C.COUNTRY_ID -- join all players
LEFT JOIN PLAYER_GAME_TYPE G ON P.PLAYER_ID = G.PLAYER_ID AND G.GAME_TYPE = 'GOLF' -- join only GOLF games
GROUP BY C.COUNTRY_CODE;

sqlize online