I have a skills management database for factory workers that has data like this:
skill 46: Thread work tap & die
skill 49: Welding proficiency
skill 51: CMM operator
skill 52: Lathe operator
skill 65: Fork Lift operator
skill 71: Portable crane operator
The JOB SKILLS table holds the skills that belong to each job title. For example: Job ID 220 is a Mechanic Level I (defined with less skills than level II). Job ID 221 is a Mechanic Level II (defined with more skills than level I).
Here's the JOB SKILLS table:
create table job_skills (job_ID int, skill_ID int, proficiency_level int);
insert job_skills (220, 46, 2);
insert job_skills (220, 49, 2);
insert job_skills (220, 51, 2);
insert job_skills (220, 52, 2);
insert job_skills (221, 46, 3);
insert job_skills (221, 49, 3);
insert job_skills (221, 51, 3);
insert job_skills (221, 52, 3);
insert job_skills (221, 65, 2);
insert job_skills (221, 71, 2);
Then there is an EMPLOYEE SKILLS table that maintains each employee badge_ID and the skills they are currently proficient in. Robert Murphy (badge_ID = 792) is currently a Mechanic Level I. He is proficient in all of the skills he is supposed to have in the Mechanic Level I skillset. Here is Robert's current information in the empl_skills table:
create table empl_skills (badge_ID int, skill_ID int, proficiency_level int);
insert empl_skills (792, 46, 3);
insert empl_skills (792, 49, 2);
insert empl_skills (792, 51, 2);
insert empl_skills (792, 52, 3);
Thirdly, The job title for Robert is stored in the employee master table:
create table empl_master (badge_ID int, firstname varchar(20), lastname varchar(20), job_ID int);
insert empl_master (792, 'ROBERT', 'MURPHY', 220);
Now finally the question: Robert Murphy gets a promotion from Mechanic I to Mechanic II. I would like to add his new skills that he is missing between Level I and Level II to the empl_skills table and set the starting proficiency level at 0. But I don't want to disturb the current skills and proficiency level records that already exist. As part of his promotion, I only want to add skill 65 and skill 71 - those are two new additional skills he will have to maintain as a Mechanic Level II. Is it possible to do sort of an INSERT INTO... WHERE NOT EXISTS somehow relating these three tables together in one SQL statement?
Also, in the promotion cycle, by the time I am ready to run my INSERT statement, Robert's empl_master record will have already been upgraded setting the job_ID from 220 to 221.
UPDATE empl_master set job_ID=221 WHERE badge_ID=792;
The empl_skills results should look like this:
badge_ID skill_ID proficiency_level
-------- -------- -----------------
792 46 3
792 49 2
792 51 2
792 52 3
792 65 0 <-- new rows difference between
792 71 0 <-- job titles 220 and 221
TIA again for your awesome help, John
By getting all skills applicable to the job and eliminating ones the employee already has (es.skill_ID IS NULL), you can get everything that is missing. You can do a WHERE NOT EXISTS or WHERE NOT IN, but I prefer this.
INSERT INTO empl_skills (badge_ID, skill_ID, proficiency_level)
SELECT
em.badge_ID,js.skill_ID,0
FROM job_skills js
INNER JOIN empl_master em ON em.job_ID=js.job_ID
LEFT JOIN empl_skills es ON es.skill_ID=js.skill_ID
es.badge_ID=em.badge_ID
WHERE es.skill_ID IS NULL
AND em.badge_ID=792