SQL Query, join returning a list

Question

I have three tables:

• tblEmployee

  employeeId int PK

  name varchar(50)

• tblSkill

  skillId int PK

  name

• tblEmployeeSkill

  employeeSkill int PK

  employeeId int FK

  skillId int FK

I want to run a query that will return all of the skills and whether a particular user has them. ie:

SQL| no

C# | yes

ruby | no

where userId = 1

any help please?

Answer 1

A simple LEFT JOIN against the list of skills should suffice for this. If the employee does not have an entry matching that skill, the primary key for that entry will be NULL:

SELECT tblSkill.name, 
       CASE 
         WHEN tblEmployeeSkill.employeeSkill IS NULL THEN 'no' 
         ELSE 'yes' 
       END as hasSkill
FROM tblSkill
LEFT JOIN tblEmployeeSkill 
  ON tblSkill.skillId = tblEmployeeSkill.skillId 
  AND tblEmployeeSkill.employeeId = 1