I'm using AdventureWorks2012 database and Microsoft SQL Server 2012. I'm trying to return SalesOrderID, Year & Month of Order Date, Total Due, Territory Group, Territory Name, State Shipping Order To, and the First and Last Name of Sales Rep assigned to the order. I used a bunch of joins and this is what I have in the code below.
SELECT OH.SalesOrderID AS SalesOrderID,
YEAR(OH.OrderDate) AS [Year],
MONTH(OH.OrderDate) AS [Month], OH.TotalDue AS TotalDue,
ST.[Group] AS [Group],
ST.Name AS TerritoryName, SP.Name AS ShipState,
(SalesP.FirstName + ' ' + SalesP.LastName) AS SalesRepName
FROM Sales.SalesOrderHeader OH
INNER JOIN Sales.SalesTerritory ST
ON OH.TerritoryID = ST.TerritoryID
INNER JOIN Sales.vSalesPerson SalesP
ON OH.SalesPersonID = SalesP.BusinessEntityID
INNER JOIN [Person].[StateProvince] SP
ON ST.TerritoryID = SP.TerritoryID
INNER JOIN [Person].[Address] A
ON OH.ShipToAddressID = A.AddressID
GROUP BY OH.SalesOrderID, OH.OrderDate,
OH.TotalDue, ST.[Group], ST.Name,
SP.Name, SalesP.FirstName, SalesP.LastName
When I execute the following code I get a bunch of SalesOrderID records and it seems to name every possible Ship State to the records. Basically I'm getting more records then I should. I'm not completely sure what I'm doing wrong in my code, I feel like my joins are right yet I'm not getting accurate data back. I'm looking for each SalesOrderID to have 1 result back, not many. Any help would be appreciated.
I think your problem possibly lies with
INNER JOIN [Person].[StateProvince] SP
ON ST.TerritoryID = SP.TerritoryID
Bearing in mind the schema structure looks the same, but I have Adventure Works 2014 installed, the following should fix your issue and reduce your result set to 1 per Sales Order.
INNER JOIN [Person].[StateProvince] SP
ON ST.TerritoryID = SP.TerritoryID
AND SP.StateProvinceID = A.StateProvinceID
The join as you had it links the single territoryID from Salesterritory with every matching entry in Stateprovince. The extra AND forces only the Stateprovince from the ship address (Address table) to match with the corresponding single stateprovince and provide a single result per order. I get a result set of 3806 records.
Please also be aware that your use of INNER JOINS means you are only returning a subset of orders. This may indeed be exactly what you require but if you need to return every Sales Order then alter the following join.
LEFT JOIN Sales.vSalesPerson SalesP