1

I am trying to add a key from one table to another so that I can create a one to many relationship. The table I would like to add a foreign key to currently has the employee's first name and last name, but not ID. I would like to query the employee table to find the ID based on their first name and last name, and then add it to the injury table.

I Keep getting the following errors: 

The multi-part identifier "InjuryOLD.FirstName" could not be bound.
The multi-part identifier "dbo.InjuryOLD.LastName" could not be bound.

Below is the SQL Statement:

Insert into dbo.InjuryOLD(dbo.InjuryOLD.EmpID)
Select dbo.EmployeeInformation.EmpID
From EmployeeInformation
Where exists(select dbo.EmployeeInformation.EmpID from dbo.EmployeeInformation
             where dbo.EmployeeInformation.FirstName = dbo.InjuryOLD.FirstName 
             and dbo.EmployeeInformation.LastName = dbo.InjuryOLD.LastName)

I have ensured that the tables and columns that I am referencing have no typo's, and I know that there is a chance that two employee could have the same name. However that is not the case in this scenario.

talbright
  • 446
  • 1
  • 10
  • 18

1 Answers1

3

The SELECT part an INSERT statement should always work on its own

so...

Select dbo.EmployeeInformation.EmpID
From EmployeeInformation
Where exists(select dbo.EmployeeInformation.EmpID from dbo.EmployeeInformation
             where dbo.EmployeeInformation.FirstName = dbo.InjuryOLD.FirstName 
             and dbo.EmployeeInformation.LastName = dbo.InjuryOLD.LastName)

...doesn't work because dbo.InjuryOLD isn't referenced in either FROM clause. You probably meant to put the dbo.InjuryOLD in the from clause of the Exists sub query instead of repeating EmployeeInformation 

Select dbo.EmployeeInformation.EmpID
From EmployeeInformation
Where exists(select 1 from dbo.InjuryOLD  
             where
                 dbo.EmployeeInformation.FirstName = dbo.InjuryOLD.FirstName 
                 and dbo.EmployeeInformation.LastName = dbo.InjuryOLD.LastName)
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • This runs fine and throws no errors, but it doesn't give me the results I wanted. It returned no values, It is querying the Injury table for ID's and I want to query the employee table for Id's. What does the "Select 1 From" in your statement do? Also perhaps "exists" is not the command I need to use. Would you happen to know? – talbright May 25 '12 at 17:41
  • Its querying the EmployeeInformation for IDs but only including where it can find a match in the InjuryOld table based on first and last names. WRT `SELECT 1` In an exists statement the [select clause is ignored](http://stackoverflow.com/q/1597442), so I always write `SELECT 1 ` so I don't have to think. – Conrad Frix May 25 '12 at 17:54
  • You were right. It was returning no results because I had labeled the first and last names backwards in the employee table. Thanks for the help. – talbright May 25 '12 at 18:06