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

Try

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

Answer 2

Hi, i am not shure why si that but FROM PeopleTable p, JobTable j can be replaced with CROSS JOIN

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

Best Regrads, Iordan

Answer 3

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

Answer 4

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