Joining Sales.SalesOrderHeader and Sales.SalesOrderDetail in AdventureWorks2019

Question

Hi!

I'm reading the book "Beginning T-SQL A Step-by-Step Approach" (4th ed.), and it's a great read. I'm learning T-SQL after getting a new position which involves working with databases.

I'm struggling to understand a particular exercise (7-5-2 page 247) in the book.
The exercise uses the AdventureWorks2019 database.

The exercise is as follows:
"Write a query using the Sales.SalesOrderHeader, Sales.SalesOrderDetail, and Production.Product tables to display the total sum of products by Name and OrderDate."

This is the solution quoted from the book:
--7.5.2
SELECT SUM(OrderQty) SumOfOrderQty, P.Name, SOH.OrderDate
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderDetailID
INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
GROUP BY P.Name, SOH.OrderDate;

I'm wondering why the INNER JOIN between SOH and SOD is not performed using SalesOrderID on both sides of the join like this:

SELECT SUM(OrderQty) SumOfOrderQty, P.Name, SOH.OrderDate
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
GROUP BY P.Name, SOH.OrderDate;

The Sales.SalesOrderDetail table has a composite primary key, where SalesOrderDetailID is a foreign key pointing back to the Sales.SalesOrderHeader table. There also seems to be only one product per order if the query joining on SOH.SalesOrderID = SOD.SalesOrderDetailID is modified to show the SOH.SalesOrderID:

SELECT OrderQty, P.Name, SOH.OrderDate, SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderDetailID
INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
order by SOH.SalesOrderID;

What is it I am missing here? Could it be a typo in the book? Previous examples join using SalesOrderID on both sides of the join. I would really appreciate if someone could help me, as I've searched the web to see if this is a common question among beginners regarding these tables in AdventureWorks, but couldn't find any clues.

Answer 1

Yeah, that looks like a typo. I don't have the 2019 edition of AdventureWorks available, but I have AdventureWorks2016, and Sales.SalesOrderDetail looks like a train-wreck to me. (SalesOrderID, SalesOrderDetailID) is the primary key, but SalesOrderDetailID is an IDENTITY column.

I typically design such a table with a row number within the order ID, so it restarts on 1 for every order. Or I use the ProductID if the business rules permit...

Answer 2

Thanks you for the answers, @Anonymous , @Tom Phillips , @LiHong-MSFT and @Erland Sommarskog ! Much appreciated. I will try to report the error.

Answer 3

There should be a section in the front about "corrections and addendums" website. I would first check there to see if it is corrected already. If not, there should be a link to submit a request to fix it.