SQL Tables Join query

Question

I think this is a problem with my joins but I can't figure it out. I am trying to pull records for employee skills where they match the requirements for positions but also keep the records of skills that employee's have that are not required for their position. Here is the table for when I pull this employee's skills.

A005122	Adamus, Fred	NHP-SAFE
A005122	Adamus, Fred	QMS 2.5
A005122	Adamus, Fred	CWRIGHT
A005122	Adamus, Fred	QMS 3.15
A005122	Adamus, Fred	NHP-ESD
A005122	Adamus, Fred	QMS 2.3
A005122	Adamus, Fred	QMS 3.14
A005122	Adamus, Fred	QMS 3.2
A005122	Adamus, Fred	QMS 2.10
A005122	Adamus, Fred	NHP-CLCK
A005122	Adamus, Fred	QMS 2.7
A005122	Adamus, Fred	NHP-PATT
A005122	Adamus, Fred	NHP-BCC
A005122	Adamus, Fred	NHP-FOD
A005122	Adamus, Fred	QMS 1.0
A005122	Adamus, Fred	QMS 3.25
A005122	Adamus, Fred	QMS 2.6
A005122	Adamus, Fred	QSL Log
A005122	Adamus, Fred	NHP-LDFC
A005122	Adamus, Fred	QMS 2.11
A005122	Adamus, Fred	QMS 2.2
A005122	Adamus, Fred	NHP-SA
A005122	Adamus, Fred	QMS 2.4
A005122	Adamus, Fred	NHP-PSAF
A005122	Adamus, Fred	NHP-QUAL
A005122	Adamus, Fred	NHP-MSDC
A005122	Adamus, Fred	NH-HOUSE
A005122	Adamus, Fred	NHP-TRN
A005122	Adamus, Fred	QMS 2.8
A005122	Adamus, Fred	QMS 2.1
A005122	Adamus, Fred	NH-TOUR
A005122	Adamus, Fred	QMS 3.50
A005122	Adamus, Fred	NHP-MFGP
A005122	Adamus, Fred	QMS 3.67

Here is the table when I run my query with the position requirements.

EmployeeID	Name	Dept	Job Title	Skill	Requirement	Descript	Date Acquired	Meets Requirement
A005122	Adamus, Fred	603	Quality Technician	CWRIGHT	CWRIGHT	Falsification Training P-101, pg14, 18b	2017-09-05 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	NHP-ESD	NHP-ESD	ESD Control	2017-04-09 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 1.0	QMS 1.0	Quality Manual	2017-04-01 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.10	QMS 2.10	Control of Solder and Flux	2019-08-07 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.11	QMS 2.11	Hand Soldering and Rework SOP	2019-08-08 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.2	QMS 2.2	Internal Audit	2017-06-21 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	NULL	VISIONSC	NULL	NULL	0
A005122	Adamus, Fred	603	Quality Technician	QMS 3.15	QMS 3.15	SDS	2017-04-09 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 3.2	QMS 3.2	Training	2020-01-20 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 3.25	QMS 3.25	MSD	2017-06-21 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 3.50	QMS 3.50	Quality Systems Instructions	2019-09-27 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 3.67	QMS 3.67	ESD Guidelines	2017-04-09 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QSL Log	QSL Log	Inspection "Instruction"	2019-09-27 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.3	QMS 2.3	Product Control	2017-06-21 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.4	QMS 2.4	Improvement	2017-06-21 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.5	QMS 2.5	Environment	2020-01-20 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.6	QMS 2.6	Analysis	2017-06-21 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.7	QMS 2.7	Control of M&M Equipment	2017-06-21 00:00:00.000	1
A005122	Adamus, Fred	603	Quality Technician	QMS 2.8	QMS 2.8	Inspection	2017-06-21 00:00:00.000	1

Here is the SQL Query

SELECT 
        emp.emp_num AS 'EmployeeID'
        ,emp.name As 'Name'
        ,pos_det.dept           AS 'Dept'
        ,pos.job_title          AS 'Job Title'
        ,emp_skills.skill       AS 'Skill'
        ,rqmt.requirement       AS 'Requirement'
        ,skill.description      AS 'Descript'
        ,emp_skills.skill_date  AS 'Date Acquired'
        ,CASE WHEN rqmt.requirement = emp_skills.skill THEN 1
        ELSE 0 
        END                     AS 'Meets Requirement'

From    employee_mst AS emp 

LEFT JOIN emp_pos_mst AS emp_pos
    ON emp.emp_num = emp_pos.emp_num
LEFT JOIN position_mst AS pos
    ON emp_pos.job_id = pos.job_id
LEFT JOIN pos_rqmt_mst AS rqmt
    ON emp_pos.job_id = rqmt.job_id

LEFT JOIN empskill_mst AS emp_skills
    ON emp.emp_num = emp_skills.emp_num 
    AND emp_skills.skill = rqmt.requirement

LEFT JOIN pos_det_mst AS pos_det
    ON pos_det.job_id = emp_pos.job_id 
    AND pos_det.job_detail = emp_pos.job_detail

LEFT JOIN skill_mst AS skill
    ON skill.skill = emp_skills.skill


WHERE emp.term_date IS NULL
and emp.emp_num IS NOT NULL

ORDER BY emp.name

Any help is greatly appreciated

Answer 1

I agree with jarlh that a simplified example would help us help you.

Reading your question a couple of times my best guess is you need to do one or more outer joins on your tables to arrive at the results i think you are aiming for.

https://www.w3schools.com/sql/sql_join_full.asp