SQL Left Joining four different tables

Question

From AdventureWorks2012, I want to write a query using the Sales.SalesOrderHeader, Sales.Customer, Sales.Store, and Person.Person tables, showing the SalesOrderID, StoreName, the customer’s first and last name as CustomerName and the salesperson’s first and last names as SalesPersonName. I want to do a left join with Sales.Customer to the Sales.Store and Person.Person tables.

Here is my work so far. However, the CustomerName and SalesPersonName both have the same information when they should be different.

SELECT soh.SalesOrderID, ST.Name AS StoreName, pp.[PersonType], pp.[FirstName] + [LastName] AS CustomerName,
pp.[FirstName] + [LastName] AS SalesPersonName
FROM Sales.SalesOrderHeader soh
JOIN Sales.Customer SC ON soh.SalesOrderID = sc.CustomerID
JOIN Sales.Store ST ON sc.CustomerID = ST.BusinessEntityID
JOIN Person.Person PP ON ST.BusinessEntityID = PP.BusinessEntityID
WHERE Persontype LIKE 'SP%'

Answer 1

You're getting bad results, because your joins are wrong. You should join on fields, that represent a relation between the 2 table. For example:

JOIN Sales.Customer sc ON soh.CustomerID = sc.CustomerID