CODEWITHSUNDEEP
sqlms-access
Jur
Jur

Reputation: 520

finding records only with ALL matching keys

I have two pairs of tables:

table AV, KEY AVNR //(this is the request)
table AVKW, KEY AVNR and KWCD //(these are the required qualifications for the request, N:1 to AV)

AND:

table EM, KEY EMNR //(this is the employee)
table EMKW, KEY EMNR and KWCD //(these are the qualifications of the employee, N:1 to EM)

Sample data:

AV:
AVNR   (descr)
10     job december 10th
20     job december 11th
30     job december 12th

AVKW:
AVNR   KWCD
10     operator
20     driving license
20     operator

(for the job on the 10th, employee must be an operator. On the 11th, he needs to be an operator AND have a driving license. No qualifications are needed on the 12th)

EM:
EMNR    (name)
60  John
70  Pete
80  Bert

EMKW:
EMNR    KWCD
60  operator
60  driving license
70  operator

(John is an operator with a driving license; Pete is operator but has no license. Bert has no qualifications at all)

For John, the query should return all AV's; for Pete, only dec. 10th and 12th; for Bert, only the 12th.

Looking from the employee, I need to know what requests he could fulfill; so I need the AV records where ALL its AVKW records have matching AVEM records (AV.KWCD=EM.KWCD) for the current employee.

(There is an extra table KW, key KWCD, listing the existing qualifications, but that's not even relevant now)

I've tried several approaches, but none seems to work... finding ONE matching qualification is easy, but the emp really needs ALL requested qualifications. How would I code this in SQL??

Thanks in advance!

Upvotes: 2

Views: 69

Answers (2)

Don George
Don George

Reputation: 1328

If you are open to using 2 queries, try these:

The first, saved as AV_EM_Match, compares every requirement with every employee and returns 0 if they are not qualified or a number >0 if they are:

SELECT AV.AVNR, EM.EMNR, EM.Name, AVKW.KWCD, Sum(IIf([AVKW].[KWCD] Is Null Or [AVKW].[KWCD]=[EMKW].[KWCD],1,0)) AS KW_Met
FROM AV LEFT JOIN AVKW ON AV.AVNR = AVKW.AVNR, EM LEFT JOIN EMKW ON EM.EMNR = EMKW.EMNR
GROUP BY AV.AVNR, EM.EMNR, EM.Name, AVKW.KWCD
ORDER BY AV.AVNR, EM.EMNR, AVKW.KWCD;

The second uses this and selects the employees qualified on every requirement:

SELECT AV_EM_Match.AVNR, AV_EM_Match.EMNR, AV_EM_Match.Name, Min(AV_EM_Match.KW_Met) AS Selected
FROM AV_EM_Match
GROUP BY AV_EM_Match.AVNR, AV_EM_Match.EMNR, AV_EM_Match.Name
HAVING (((Min(AV_EM_Match.KW_Met))>0));

This also works with jobs with no requirements and employees with no qualifications - all 3 employees are selected for the 3rd job.

You can switch the order of the fields in the 2nd query if you want them sorted by employee instead of by job.

Upvotes: 0

Clay
Clay

Reputation: 5084

I think the most straightforward way is to select the employee that has a count of matching required qualifications equal to the count of actual qualifications:

select * from
(
  select
    av.avnr,
    count(*) as qualificationCount
  from 
    avkw 
    inner join 
    av on
      avkw.avnr = av.avnr
  group by
    av.avnr
) as qualification  

inner join

(
  select
    em.emnr, 
    avkw.avnr,
    count(*) qualificationCount
  from
    emkw 
    inner join
    em on
      emkw.emnr = em.emnr
    inner join
    avkw on
      emkw.kwcd = avkw.kwcd
  group by
    em.emnr, 
    avkw.avnr
) as qualified

on
  qualifications.avnr = qualified.avnr
where
  qualifications.qualificationCont = qualified.qualificationCount

This should list all the employees that exactly match each available position.

Upvotes: 1

Related Questions