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

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

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

labor = List of labor codes (laborcode, name)

10072   John Doe
10073   Jane Smith
10074   Mark Twain

craft = List of craft codes (craft, desc)

MECH   Mechanic
ELEC   Electrician
PLUM   Plumber

laborcraft = the craft attached to each labor code (laborcode, craft)
```
10072   MECH
10073   ELEC
10074   PLUM
```
qualcraft = qualifications relevant to each craft (qualcode, craft)
```
HSE-01   MECH
HSE-02   ELEC
HSE-03   PLUM
```
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
)

Advertisement

Answer