table join

[WorkerBeeJ]

I'm a newbee with SQL, so I'm very sorry for what is probably a very easy question. I have two tables, one table called Employee, one field of which is EmployeeName, and the another table called EmployeeHistory, which records any Employee record changes such as a name change.

The fields for EmployeeHistory are:
EmployeeHistoryID, EmployeeID (FK), ColumnChanged, OldValue, NewValue

This table gets an entry for each change made to an employee record, so if, for instance, someone changes their name three times, the Employee History table will have three entries for these changes.

I need to create an employee search that allows searching on an employee's current name as well as previous names. For each employee who matches the search criteria on either their current or previous name, search should return the current name and a comma separated list of all their previous names.

What do I need to add to the following query to get the list of previous employee names from the second table?

SELECT Employee.EmployeeName, ...
FROM Employee
WHERE Employee.EmployeeName like '%tyler%'


thank you!!

 

[r937]

select e.EmployeeID 
     , e.EmployeeName
     , eh.OldValue
  from Employee  as e
inner
  join EmployeeHistory  as eh
    on e.EmployeeID = eh.EmployeeID
 where eh.ColumnChanged = 'EmployeeName'
   and eh.OldValue like '%tyler%'
union all    
select EmployeeID 
     , EmployeeName
     , null
  from Employee  
 where EmployeeName like '%tyler%'

this does not produce the comma-delimited list, you'll have to do that with procedural code

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)