Missing records in a -6 table matrix

i would be grateful for any help in solving the problem below. I’ve restated the problem following a request for some data

I have the following sql tables

1. qual = List of qualification codes (qualcode, description) e.g.

   HSE-01  HSE INDUCTION
   HSE-02  SAFETY BRIEFING
   HSE-03  FIRST AID
   

2. labor = List of labor codes (laborcode, name)

   10072   John Doe
   10073   Jane Smith
   10074   Mark Twain
   

3. craft = List of craft codes (craft, desc)

   MECH   Mechanic
   ELEC   Electrician
   PLUM   Plumber
   

4. laborcraft = the craft attached to each labor code (laborcode, craft)

   10072   MECH
   10073   ELEC
   10074   PLUM
   

5. qualcraft = qualifications relevant to each craft (qualcode, craft)

   HSE-01   MECH
   HSE-02   ELEC
   HSE-03   PLUM
   

6. laborqual = actual qualifications of each labor id (laborcode, qualcode)

   10072   HSE-01
   10073   HSE-01
   10072   HSE-02
   10074   HSE-02
   

Each Qualification code may be relevant for one or many crafts, Each labor code has a single craft

From the data in table 5 above you can see that qualification HSE-03 is relevant for the PLUM craft and HSE-02 is relevant for the ELEC craft

However from table 6 (actual qualifications) we can see that

• Mark Twain is a PLUM craft but does not possess the HSE-03 qualification
• Jane Smith is an ELEC but does not possess the HSE-02 qualification.

I need to identify all the qualifications that SHOULD be held by each labor code but ARE NOT. the results should look like

    10073   HSE-02
    10074   HSE-03

I don’t mind if labor codes have other qualifications that they don’t need to have for example

    10072   HSE-02
    10073   HSE-01
    10074   HSE-02

I just need to know where we’re missing the relevant qualifications

Thanks for any help

Answer

You could join laborcraft and qualcraft, and use not exists to flter on “missing” rows in laborqual:

select lc.laborcode, qc.qualcode
from laborcraft lc
inner join qualcraft qc on qc.craft = lc.craft
where not exists (
    select 1 
    from laborqual lq 
    where lq.laborcode = lc.laborcode and lq.qualcode = qc.qualcode
)