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?

CodePudding user response：

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.