multi-part identifier could not be bound [closed]

Question

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?

Answer 1

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.

Good luck.