What to do when multi part identifier could not be bound?
I have 3 tables: table_a needs to communicate with table_c, but has to go to table_b first. Or put differently: table_a refers to table_b which then refers to table_c.
The relationship between the tables is defined as:
table_a.licno relates to table_b.licno
table_b.ID relates to table_c.ID
I need to retrieve 3 columns from table_c like the following:
table_c.lastname
table_c.firstname
table_c.middlename
Here's my code so far:
select firstname, middlename, lastname
from table_c
where table_b.licno=table_a.licno
and table_c.employeeid = table_b.employeeid;
I am receiving the following error message:
multi-part identifier could not be bound
What am I doing wrong?
If multi part identifier could not be bound -
That's as simple as adding the tables you are joining to the FROM part of your query.
select firstname,
middlename,
lastname
from table_c,
table_b,
table_a
where table_b.licno = table_a.licno
and table_c.employeeid = table_b.employeeid;The statement can only join information from tables which are referenced in the FROM part of the SELECT statement. You can also use the newer JOIN notation which might give you an idea why your statement didn't work the first time round:
SELECT firstname,
middlename,
lastname
FROM table_c
JOIN table_b
ON table_c.employeeid = table_b.employeeid
JOIN table_a
ON table_b.licno = table_a.licno;
You can then go on to alias (give the tables a short name) the referenced tables, to shorten the statements:
SELECT firstname,
middlename,
lastname
FROM table_c AS c
JOIN table_b AS b
ON c.employeeid = b.employeeid
JOIN table_a AS a
ON b.licno = a.licno;The error message in the title of your question, stems from the fact that you are trying to reference objects in your WHERE clause, which aren't listed in the FROM.
multi-part identifier could not be bound
A multi-part identifier is something like:
database_name.schema_name.table_name.column_name
In your statement the following bit is a multi-part identifier:
table_c.employeeid
^ ^
| +--------- column_name
+-------------------- table_name
Some systems support even the addition of a server_name.