Geek Answers Handbook

SQL error: multicast identifier "table_name .ColumnName" could not be associated

When an LEFT JOINing table in an SQL query, sometimes I need to reference multiple tables in an 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 may result in this error:

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

It seems that the ON clause in the LEFT JOIN statement can only "see" the last table specified in the FROM list. It's true? Are there any workarounds?

+7
sql sql-server tsql
source share
4 answers

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

+7
source share

While cross-join syntax is a direct translation of what you have provided, this may not be right for your situation. This would link all people to all jobs before they joined the pay table. It does not seem likely that this is what you want.

Do you really have people who are not connected with salaries? In this case, do you want to see any jobs that are not related to salaries or people? Sample data and a result set will 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
+3
source share

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
0
source share

I do not understand why si, 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 barriers, Jordan

0
source share

More articles:


All Articles