I have this query

SELECT
  U.Country,
  U.Product,
  Week,
  SUM(
    CASE WHEN Year = 2022
    THEN SoldUnits ELSE 0 END
  ) AS TotalSoldUnits
FROM Uploads U
INNER JOIN MaxWeek_Product T1
ON T1.Product = U.Product
  AND MaxWeek = Week
where U.country = 'GT'
GROUP BY U.Country, U.Product
ORDER BY U.Country;

It returns me something like this:

The problem is that It has to return 500 rows and actually returns 480 rows. I found that the problems is in AND MaxWeek = Week because some Products has Week = 0 in the table MaxWeek_Product but in the Uploads table, none of the Products have Week = 0. And those Products get lost in the INNER JOIN.

I tried using LEFT JOIN or RIGHT JOIN but still, it doesn't work.

What can I do to retrieve those missing Products with TotalSoldUnits = 0.

Uploads table:

MaxWeek_Product table:

CodePudding user response：

The main issue here is the WHERE statement, because it activates after the JOIN operations have taken effect, hence removing the rows that the LEFT/RIGHT JOIN could have saved (with NULL values).

Here is how it becomes after the changes:

SELECT
  T1.Country,
  T1.Product,
  T1.MaxWeek,
  SUM(CASE WHEN Year = 2022
           THEN SoldUnits 
           ELSE 0 
      END)                      AS TotalSoldUnits
FROM      MaxWeek_Product T1
LEFT JOIN Uploads U
       ON T1.Product = U.Product
      AND T1.MaxWeek = U.Week
      AND T1.country = 'GT'
GROUP BY T1.Country, 
         T1.Product, 
         T1.MaxWeek
ORDER BY T1.Country;

Try it at the SQL fiddle here.