You are here: Blog - Troubleshooting

The multi-part identifier could not be found

When you get the below error message, at first you might be puzzled:
"The multi-part identifier [object_name] could not be found"

However, don't worry. It is rather a spelling mistake in your query that caused the above error.

Consider the below example:

--Create Sample DB and Tables
CREATE DATABASE TestDB1;
GO

USE TestDB1;
GO

CREATE TABLE tblStudents(
id INT,
studentName VARCHAR(50)
);
GO

CREATE TABLE tblClassesStudents(
classID INT,
studentID INT,
schoolYear INT
);
GO

--Let's populate the tables with sample data
INSERT    INTO dbo.tblStudents
        ( id, studentName )
VALUES  ( 101, 'Student 1'),
        ( 102, 'Student 2'),
        ( 103, 'Student 3'),
        ( 104, 'Student 4'),
        ( 105, 'Student 5');
GO

INSERT INTO    dbo.tblClassesStudents
        ( classID, studentID, schoolYear )
VALUES  ( 200,101,2016),
        ( 200,102,2016),
        ( 200,103,2016),
        ( 200,104,2016),
        ( 300,104,2016);
GO
        

--Now let's run the below query in order to get the classes students took for 2016
SELECT s.id ,
       s.studentName,
       c.classID
FROM dbo.tblStudents s
    INNER JOIN dbo.tblClassesStudents c ON s.id=c.studentID
WHERE c.schoolYear=2016;
GO

We can see that the query executes successfully and returns the results.

--Now let's run the same query with an intentional spelling mistake
SELECT s.id ,
       s.studentName,
       d.classID
FROM dbo.tblStudents s
    INNER JOIN dbo.tblClassesStudents c ON s.id=c.studentID
WHERE c.schoolYear=2016;
GO

output:
Msg 4104, Level 16, State 1, Line 53
The multi-part identifier "d.classID" could not be bound.

As you can see we are referencing a non-existing column because "d.classID" is not available (instead, "c.classID" is available).

Resolution: Use the proper table alias in your query (c.classID).



--Now let's run the same query without a FROM clause 
SELECT s.id ,
       s.studentName,
       d.classID

output:
Msg 4104, Level 16, State 1, Line 65
The multi-part identifier "s.id" could not be bound.
Msg 4104, Level 16, State 1, Line 66
The multi-part identifier "s.studentName" could not be bound.
Msg 4104, Level 16, State 1, Line 67
The multi-part identifier "d.classID" could not be bound.

Resolution: Use the FROM keyword.

As you can see, you can get the "multi-part" identifier error for a number of reasons:
1. Using a non-existing table alias or table name in your query
2. Mistakenly skipping the FROM clause in your query
3. Trying to use a table alias which was defined in a subquery outside the subquery

Whenever you get this error message, make sure you check the above three possible reasons!