CODEWITHSUNDEEP
sqlsql-serversql-server-2005syntax
Melanie
Melanie

Reputation: 1429

SQL Server 2005 The multi-part identifier "x" could not be bound

I think I'm having a syntax problem, but I'm struggling to find an answer...

Could anyone explain why

SELECT TOP 3 * FROM Facilities 
        INNER JOIN FacilityStates 
        ON FacilityStates.Asset = Facilities.ID
        WHERE Facilities.ID = 'MyFacility'

compiles and returns a suitable result, but what I think is the fully qualified version 

SELECT TOP 3 * FROM [dbo].[Facilities] 
               INNER JOIN [dbo].[FacilityStates]
               ON [dbo].[FacilityStates.Asset] = [dbo].[Facilities.ID]
               WHERE [dbo].[Facilities.ID] = 'MyFacility'

throws "The multi-part identifier "x" could not be bound." for the left and right parts of the 'on' clause and the 'where' clause?

Upvotes: 3

Views: 4367

Answers (3)

Oleg Dok
Oleg Dok

Reputation: 21766

Try to figure out that everything inside square brackets is one name. You framed with sq. brackets table and field names together. The valid query is:

SELECT TOP 3 * 
FROM [dbo].[Facilities] 
INNER JOIN [dbo].[FacilityStates]
  ON [dbo].[FacilityStates].[Asset] = [dbo].[Facilities].[ID]
WHERE [dbo].[Facilities].[ID] = 'MyFacility'

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300599

It's your placement of square brackets:

SELECT TOP 3 * FROM [dbo].[Facilities]  
               INNER JOIN [dbo].[FacilityStates] 
               ON [dbo].[FacilityStates].[Asset] = [dbo].[Facilities].[ID] 
               WHERE [dbo].[Facilities].[ID] = 'MyFacility'

Upvotes: 1

user596075
user596075

Reputation:

Because you are combining your Table.Column in one set of square brackets:

....
ON [dbo].[FacilityStates.Asset] = [dbo].[Facilities.ID] 
....

This should be:

....
ON [dbo].[FacilityStates].[Asset] = [dbo].[Facilities].[ID] 
....

Square brackets in SQL Server are used to explicitly denote an object name that could contain spaces or be a reserved word. So when you combined FacilityStates.Asset within one set of brackets, you were telling SQL Server that there is an object with that name. Which there is not.

Upvotes: 3

Related Questions