The multi-part identifier “dbo.Employee Master Table Data.Employee Number” could not be bound

Question

Post reply

The multi-part identifier “dbo.Employee Master Table Data.Employee Number” could not be bound

gary-955053

SSC Rookie

Points: 46
More actions
May 27, 2009 at 4:54 am

#95580

I get an error when running the below script - The multi-part identifier “dbo.Employee Master Table Data.Employee Number” could not be bound.
Any ideas anyone?
SELECT TOP (100) PERCENT dbo.Employees.ESNumber, [Employee Number] AS EmployeeID_SH, Title AS Title_SH, [First Name] AS Firstname_SH,
Surname AS Surname_SH, [Home E-Mail] AS [Payslip_E-Mail]
FROM HRSELECTDB.selecthr.dbo.[Employee Master Table Data] AS [Employee Master Table Data_1] INNER JOIN
dbo.Employees ON dbo.[Employees Master Table Data].[Employee Number] = dbo.Employees.EmployeeID_SH
ORDER BY EmployeeID_SH
Regards,
Gary

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

The problem is with the table qualifier used in the ON Clause for column "Employee Number".

.....

FROM HRSELECTDB.selecthr.dbo.[Employee Master Table Data] AS [Employee Master Table Data_1] INNER JOIN

dbo.Employees ON dbo.[Employees Master Table Data].[Employee Number] = dbo.Employees.EmployeeID_SH

ORDER BY EmployeeID_SH

It should read as

...

dbo.Employees ON [Employees Master Table Data_1].[Employee Number] = dbo.Employees.EmployeeID_SH

And also, as a standard practice, I suggest you to add alias to all tables, keep aliases short & always qualify columns.

--Ramesh

gary-955053 SSC Rookie Points: 46 More actions · Answer 2

Excellent, thank you so much

I take on board you comment too!

Thanks,

G

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 3

Another thing I would suggest is to minimize the use of special characters in database and object names. Instance names you can't always avoid having special characters (\ for example). But in an existing database you have to deal with it of course.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Hopefully I did it correctly for your databases and tables, but this is how I would have written and formatted your code. I am curious how you are using this query. The reason I am asking is due to the presence of the SELECT TOP (100) PERCENT at the beginning and the ORDER BY at the end.

SELECT TOP (100) PERCENT

Emp.ESNumber,

EmpMasterData.[Employee Number] AS EmployeeIDSH,

EmpMasterData.Title AS TitleSH,

EmpMasterData.[First Name] AS FirstnameSH,

EmpMasterData.Surname AS SurnameSH,

EmpMasterData.[Home E-Mail] AS PayslipEMail

FROM

HRSELECTDB.selecthr.dbo.[Employee Master Table Data] AS EmpMasterData

INNER JOIN dbo.Employees AS Emp

ON EmpMasterData.[Employee Number] = Emp.EmployeeID_SH

ORDER BY

EmployeeIDSH