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.
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
19 People are following this question.
