Query to return 2 different names in same record

I am trying to build a database to keep a record of skills held by employees
in our company. I have 3 tables Employee, Employee Skills, and Skills.
In the employee skills table I want the name of the employee and the name of
the assessor who tested the skill. Both these names are in the employee
table. In the Employee Skills table I have 2 foreign keys to the EmpID in the
Employees table, one of EmpID for the employee name and another of
AssesorEmpID to record the name of the assesor.
I want to return the name of the employee and the name of the assesor in a
query to show the employees skill level and who approved it. I know I could
use a look up and store the actual name of the assessor but was hoping to
keep things clean by using just the EmpID and AssesorEmpID from the employees
table.
Is this possible?

On Sat, 4 Oct 2008 03:15:01 -0700, PeterW wrote:

>I am trying to build a database to keep a record of skills held by employees
>in our company. I have 3 tables Employee, Employee Skills, and Skills.
>In the employee skills table I want the name of the employee and the name of
>the assessor who tested the skill. Both these names are in the employee
>table. In the Employee Skills table I have 2 foreign keys to the EmpID in the
>Employees table, one of EmpID for the employee name and another of
>AssesorEmpID to record the name of the assesor.
>I want to return the name of the employee and the name of the assesor in a
>query to show the employees skill level and who approved it. I know I could
>use a look up and store the actual name of the assessor but was hoping to
>keep things clean by using just the EmpID and AssesorEmpID from the employees
>table.
>Is this possible?

Hi Peter,

Yes, it is.

SELECT e.EmpName AS Employee,
a.EmpName AS Assesor,
s.SkillName AS Skill
FROM EmployeeSkills AS es
INNER JOIN Employee AS e
ON e.EmpID = es.EmpID
INNER JOIN Employee AS a
ON a.EmpID = es.AssesorEmpID
INNER JOIN Skills AS s
ON s.SkillID = es.SkillID;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis