Northwind DB Customers and Suppliers tables both have Country column (there is no Country table in the DB, so the Country field is not a FK).
Argentina has no suppliers, but 3 customers.
Task: "Produce a full list of countries (derived from Customers and Suppliers tables) with total number of customers and suppliers for every country. In case a country has no (NULL) customers or suppliers, put 0 (zero)".
Solution:
USE Northwind
GO
WITH
CountryList AS
(SELECT Country FROM Customers UNION SELECT Country FROM Suppliers),
SupplierCountry AS
(SELECT Country, TotalSuppliers=COUNT(*) FROM Suppliers GROUP BY Country),
CustomerCountry AS
(SELECT Country, TotalCustomers=COUNT(*) FROM Customers GROUP BY Country)
SELECT cl.Country, ISNULL(sc.TotalSuppliers,0) C, ISNULL(cc.TotalCustomers,0) TotalCustomers
FROM CountryList cl LEFT JOIN SupplierCountry sc ON cl.Country=sc.Country
LEFT JOIN CustomerCountry cc ON sc.Country=cc.Country
The expected result for Argentina is:
Country TotalSuppliers TotalCustomers
Argentina 0 3
Instead, the result is:
Argentina 0 0
(same pattern for every other country with no suppliers but some customers)
As far as understand, the 1st LEFT JOIN produces an intermediate result "Argentina 0". The 2nd LEFT JOIN should join this result with Argentina's customers count (3).
What do I miss?
Your last join currently
LEFT JOIN CustomerCountry cc ON sc.Country=cc.Country
should actually be
Left JOIN CustomerCountry cc ON cl.Country=cc.Country
If there are any suppliers for a country, then sc.country is the same as cl.country (due to preceding join), but if there are no suppliers for a country, then sc.Country will be null.