question

Gehima2016 avatar image
Gehima2016 asked

Four Table Join Syntax

--Get SalesOrderID, CustomerID, TerritoryID and TotalDue for highest amount ordered in 2007 Question: Get SalesOrderID, CustomerID, TerritoryID and TotalDue for highest amount ordered in 2007 SELECT h.[SalesOrderID], c.[CustomerID], t.[TerritoryID], s.[CardNumber] FROM [Sales].[SalesOrderHeader] h LEFT JOIN [Sales].[Customer] c ON h.[CustomerID] = c.[CustomerID] LEFT JOIN [Sales].[SalesTerritory] t ON h.[TerritoryID] = t.TerritoryID LEFT JOIN [Sales].[CreditCard] s ON h.[CreditCardID] = s.CreditCardID WHERE [TotalDue] = (SELECT max([TotalDue]) FROM [Sales].[SalesOrderHeader] WHERE YEAR([OrderDate]) = 2007) The [Sales].[SalesOrderHeader] is the main table that has CustomerID, TerritoryID, CreditCardID as foreign keys on the table. The SalesOrderID is retrieved from here to join the result set. CustomerId column is a foreign key in the [Sales].[SalesOrderHeader] table is used to join CustomerId column which is a primary in [Sales].[Customer] table. The CustomerID is retrieved from here to join the result set. TerritoryID column is a foreign key in the [Sales].[SalesOrderHeader] table is used to join TerritoryID column which is a primary in [Sales].[SalesTerritory] table. The TerritoryID is retrieved from here to join the result set. CreditCardID column is a foreign key in the [Sales].[SalesOrderHeader] table is used to join CreditCardID column which is a primary in [Sales].[CreditCard] table and the credit card number is retrieved from this table to the result set.    
tablejoinsyntax
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So what's the question?
1 Like 1 ·

1 Answer

· Write an Answer
Avi1 avatar image
Avi1 answered
Join syntax looks right. but instead of using subquery you can use order by clause and TOP 1 (in the select clause) WHERE YEAR([OrderDate]) = 2007 ORDER BY totalDue desc
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.