AdventureWorks queries

Question

I am trying to answer this question on AdventureWorks:

Print the information about all the Sales.Person and their sales quota. For every Sales person you should provide their FirstName, LastName, HireDate, SickLeaveHours and Region where they work.

This is what I have been able to do:

SELECT 
    Person.FirstName, Person.LastName, 
    Employee.HireDate, Employee.SickLeaveHours, 
    SalesPerson.SalesQuota, SalesTerritory.CountryRegionCode
FROM
    Person.Person, HumanResources.Employee, 
    Sales.SalesPerson, Sales.SalesTerritory
WHERE
    Person.BusinessEntityID = Employee.BusinessEntityID;

but it doesn't completely answer the question and I am struggling to connect tables together.

Answer 1

Use proper ANSI INNER JOINs to connect the tables, and preferably also use table aliases to make your query more approachable and more readable:

SELECT 
    p.FirstName, p.LastName, 
    e.HireDate, e.SickLeaveHours, 
    sp.SalesQuota, st.CountryRegionCode
FROM
    Person.Person p
INNER JOIN 
    HumanResources.Employee e ON e.BusinessEntityID = p.BusinessEntityID
INNER JOIN
    Sales.SalesPerson sp ON sp.BusinessEntityID = e.BusinessEntityID
INNER JOIN
    Sales.SalesTerritory st ON st.TerritoryID = sp.TerritoryID