Search code examples
sqlouter-join

MS SQL Join function anomaly


I ran this query, which looks correct. However, I noticed that the that the output looks like a bunch of duplicate text (See image below). Is something wrong with the code? If so, how do I correct this anomaly

SELECT          sv.FirstName + '  ' + sv.LastName 'SalesPersonName',
                st.[Group] 'TerritoryGroup',st.Name 'TerritoryName',
                StateProvinceName 'ShipState',
                YEAR(OrderDate) 'Order Year',MONTH(OrderDate) 'Order Month',
                (soh.TotalDue) Total
FROM                Person.StateProvince ps
LEFT OUTER JOIN         Sales.SalesOrderHeader soh 
ON              ps.TerritoryID=soh.TerritoryID
LEFT OUTER JOIN         Sales.SalesTerritory st
ON              soh.TerritoryID = st.TerritoryID
LEFT OUTER JOIN         Sales.vSalesPerson sv
ON              soh.SalesPersonID = sv.BusinessEntityID

enter image description here


Solution

  • This query ran with no repeat rows

    SELECT      sv.FirstName+'  '+sv.LastName 'SalesPersonName',st.[Group]     'TerritoryGroup',st.Name 'TerritoryName', ps.Name 'ShipState', 
        YEAR(OrderDate) 'OrderYear',MONTH(OrderDate) 'OrderMonth',soh.TotalDue
    FROM        Person.Address pa
    INNER JOIN  Person.StateProvince ps
    ON      pa.StateProvinceID = ps.StateProvinceID 
    INNER JOIN  Sales.SalesOrderHeader soh
    ON      pa.AddressID = soh.BillToAddressID 
    AND     pa.AddressID = soh.ShipToAddressID 
    INNER JOIN  Sales.SalesTerritory st
    ON      ps.TerritoryID = st.TerritoryID 
    AND     ps.TerritoryID = st.TerritoryID 
    AND     soh.TerritoryID = st.TerritoryID 
    AND     soh.TerritoryID = st.TerritoryID 
    CROSS JOIN  Sales.vSalesPerson sv