SQL Error: The multi-part identifier "tableName.ColumnName" could not be bound

Question

When LEFT JOINing tables in a SQL query, sometimes I need to reference multiple tables in the ON clause. For example:

SELECT p.Name, j.Job, s.Salary
FROM PeopleTable p, JobTable j
LEFT JOIN SalaryTable s ON s.PeopleID=p.PeopleID AND s.JobID=j.JobID

However, the above would give this error:

SQL Error: The multi-part identifier "p.PeopleID" could not be bound.

It seems that the ON clause in a LEFT JOIN statement can only "see" the last table listed in the FROM list. Is this true? Any workarounds?

Answer 1

You cannot mix the SQL-89 Join syntax "table, table" with the SQL-92 Join syntax "table LEFT JOIN table ON condition"

Answer 2

While the cross join syntax is a direct translation of what you provided, it may not be right for your situation. It would associated all people to all jobs before left joining to the salary table. This does not seem likely that this is what you want.

Do you really have any people who aren't associated with a salary? For that matter do you want to see any jobs which are not associated with a salary or people? Sample data and result set would help us give you a query that does what you really need. I suspect one of the following might give you better results:

SELECT p.Name, j.Job, s.Salary
FROM  PeopleTable p
JOIN SalaryTable s  ON s.PeopleID=p.PeopleID
RIGHT JOIN JobTable j  ON s.JobID=j.JobID

SELECT p.Name, j.Job, s.Salary
FROM  PeopleTable p
JOIN SalaryTable s  ON s.PeopleID=p.PeopleID
JOIN JobTable j  ON s.JobID=j.JobID

SELECT p.Name, j.Job, s.Salary
FROM SalaryTable s 
RIGHT JOIN PeopleTable p  ON s.PeopleID=p.PeopleID
RIGHT JOIN JobTable j  ON s.JobID=j.JobID