SQL join a table with an indirect foreign key

Question

I am trying to select 2 columns from 3 tables that are indirectly linked

dbo.Person

BusinessID	Name
001	Haley Keller
002	Ceila Mark

EmployeeDepartmentHistory

BusinessID	DeptID
001	201
002	104

HumanResources.Department

DeptID	Name
104	Accounting
201	Sales

I tried;

SELECT P.Name AS NAME, E.DeptID AS DEPARTMENT
FROM dbo.Person P
JOIN HumanResources.Department E ON E.DeptID = (SELECT
B.DeptID FROM HumanResources.EmployeeDepartmentHistory B
WHERE P.BusinessID IN (SELECT BusinessID FROM
dbo.Person))

But it just displays an empty table view with the columns NAME | DEPARTMENT

Answer 1

You only get one FROM clause per statement, but you can have as many JOIN clauses as you wish inside of it to establish the relationships between tables used in your SELECT.

SELECT P.Name AS NAME, E.DeptID AS DEPARTMENT
FROM dbo.Person P
    JOIN HumanResources.EmployeeDepartmentHistory B
       ON P.BusinessID = B.BusinessID
    JOIN HumanResources.Department E 
        ON B.DeptID = E.DeptId