UNION NULLS into View that can be queried later

Question

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 (EXP). You can see in the jobskills table that Apprentices must be able to Cut, Drill, and Bend (10, 20, 30). Experts should be able to Cut, Drill, Bend, Weld, and Turn on a lathe (10 thru 50).

jobskills table:

job_code skill_desc skill_ID
-------- ---------- --------
APP      Cut              10
APP      Drill            20
APP      Bend             30
EXP      Cut              10
EXP      Drill            20
EXP      Bend             30
EXP      Weld             40
EXP      Turn             50

Likewise, the company has three employees, Al, Tom, and Bob, who don't exactly have all of the skills they are supposed to have. The target case is the Expert- Bob who is missing the critical Bend skill (30) he should have developed as an Apprentice but didn't.

emplskills table:

empl_ID emplName job_code skill_ID
------- -------- -------- --------
    307 Al       APP            10
    307 Al       APP            20
    307 Al       APP            30
    396 Tom      APP            10
    396 Tom      APP            20
    426 Bob      EXP            10
    426 Bob      EXP            20
    426 Bob      EXP            40
    426 Bob      EXP            50

I'm trying to push the outer join nulls into a view with the matching records so that an application that queries skill data can look up Bob by empl_ID=426 and see his current skills AND his missing skills. Ultimately, I need to get to the result view below:

DESIRED RESULT:

empl_ID emplName job_code skill_ID
------- -------- -------- --------
    426 Bob      EXP            10
    426 Bob      EXP            20
    426 {null}   {null}         30
    426 Bob      EXP            40
    426 Bob      EXP            50

I've tried something like this:

(select t1.empl_ID, t1.emplName, t2.job_code, t1.skill_ID 
 from emplskills t1, jobskills t2 
 where t1.skill_ID = t2.skill_ID AND t1.job_code = t2.job_code)
 UNION
(select t1.empl_ID, t1.emplName, t2.job_code, t2.skill_ID 
 from jobskills t2 left outer join emplskills t1 
 on t2.skill_ID = t1.skill_ID AND t2.job_code = t1.job_code 
 where t1.empl_ID is null);

I get the expected cartesian join with the NULL (30) skill row.

UNION join result:

empl_ID emplName job_code skill_ID
------- -------- -------- --------
 {null} {null}   EXP            30
    307 Al       APP            10
    307 Al       APP            20
    307 Al       APP            30
    396 Tom      APP            10
    396 Tom      APP            20
    426 Bob      EXP            10
    426 Bob      EXP            20
    426 Bob      EXP            40
    426 Bob      EXP            50

But there are two problems here: (a) When I query the view to see Bob's skills (select where empl_ID=426) I'm not going to get the NULL (30) row I need to see. (b) You'll notice that the Apprentice- Tom is also missing the Bending skill (30). So who does the NULL (30) row belong to?

Is it even possible to stand up one dummy column in the UNION to propagate these missing NULLs associated with the empl_ID like in the desired result above?

TIA, John

Answer 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 (
   SELECT DISTINCT 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