Solved Multiple JOINs issue (1 Viewer)

[mishash]

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.
"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?

 

[sonic8]

You must join CustomerCountry to the CountryList not SupplierCountry, which contains no record for Argentina.

 

[mishash]

You must join CustomerCountry to the CountryList not SupplierCountry, which contains no record for Argentina.

1. How do I do it in the same FROM statement?
2. In my example I join CustomerCountry to the result set of the 1st JOIN (which has a record for Argentina) , not to SupplierCountry. Don't I?

 

[sonic8]

2. In my example I join CustomerCountry to the result set of the 1st JOIN (which has a record for Argentina) , not to SupplierCountry. Don't I?

No, you generally don't join to result sets of previous joins (unless they are encasuplated as a sub-select), you join to the individual tables.

You wrote: ... LEFT JOIN CustomerCountry cc ON sc.Country=cc.Country
This compares the Country colum of sc (=SupplierCountry), which does not contain a record for Argentina.
Instead you must compare to the Country from CountryList: ... LEFT JOIN CustomerCountry cc ON cl.Country=cc.Country

 

[mishash]

No, you generally don't join to result sets of previous joins (unless they are encasuplated as a sub-select), you join to the individual tables.

You wrote: ... LEFT JOIN CustomerCountry cc ON sc.Country=cc.Country
This compares the Country colum of sc (=SupplierCountry), which does not contain a record for Argentina.
Instead you must compare to the Country from CountryList: ... LEFT JOIN CustomerCountry cc ON cl.Country=cc.Country

1. Thank you very much! Your fix did the job.
2. I was confused by this article which explicitly says that in multiple JOINs the following JOIN joins a table to the previous (intermediate) result set (not to the previous table).
https://blog.quest.com/an-overview-...mples/#:~:text=SQL joins with multiple tables

SQL joins with multiple tables​

In the previous examples, we use two tables in a SQL query to perform join operations. Mostly, we join multiple tables together and it returns the relevant data.

The below query uses multiple inner joins.

USE [AdventureWorks2019]
GO
SELECT
e.[BusinessEntityID]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,e.[JobTitle]
,d.[Name] AS [Department]
,d.[GroupName]
,edh.[StartDate]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON e.[BusinessEntityID] = edh.[BusinessEntityID]
INNER JOIN [HumanResources].[Department] d
ON edh.[DepartmentID] = d.[DepartmentID]
WHERE edh.EndDate IS NULL
GO

Let’s analyze the query in the following steps:

• Intermediate result 1: First inner join is between [HumanResources].[Employees] and [Person].[Person] table.
• Intermediate result 2: Inner join between the [Intermediate result 1] and [HumanResources].[EmployeeDepartmentHistory] table.
• Intermediate result 3: Inner join between the [Intermediate result 2] and [HumanResources].[Department] table.

 

[sonic8]

2. I was confused by this article which explicitly says that in multiple JOINs the following JOIN joins a table to the previous (intermediate) result set (not to the previous table).

I'm not too impressed with that quoted article in general. There are quite a few phrasings I find hard to understand and some outright misleading. - The stuff about the "intermediate results" is definitely in the latter category.
The internal processing of the database engine will likely involve those mentioned "intermediate results", but they are not something you should concern yourself when writing the query.