Union Nulls Into View That Can Be Queried Later

Microsoft SQL SERVER: I am working on a skills matrix problem. The example below is a simplified scenario. A company has a factory with two job titles: Apprentice (APP) and Expert

Solution 1:

You want use JOIN to get required skills for each job, then use the LEFT JOIN to found out which one are missing.

SQL DEMO

WITH required_skills as (
   SELECTDISTINCT e.empl_ID, e.job_code, j.skill_ID
   FROM emplskills e
   JOIN jobskills j
     ON e.job_code = j.job_code
)
SELECT *
FROM required_skills r
LEFT JOIN emplskills e
   ON r.empl_ID = e.empl_ID
  AND r.skill_ID = e.skill_ID