-2

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?

  • Provide all tables DDLs and the query text. – Akina Sep 15 at 5:17
  • Your WHERE references tables a & b, but those tables aren't JOINed in your main query. – AMtwo Sep 15 at 5:33
  • i'm sorry, i'm unfamiliar with the INNER JOIN function, can you help me out how do I do that? thanks – Chito H Sep 15 at 6:04
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.

| improve this answer | |
  • thanks very much, sorry for being late for reply it worked – Chito H Sep 22 at 1:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.