SELECT if multiple entries exist in lookup table

Hi all,

I’m working on an IT recruitment database.

My particular problem involves searching for candidates according to their skills.

I have a table for candidates and a table for edit_qualifiers (skills). You can associate an unlimited number of Edit Qualifiers with a Candidate. Therefore, for every Edit Qualifier that applies to a Candidate, a create a row within a lookup table (candidates_edit_qualifiers_lookup) containing: id, candidate, edit_qualifier.

What I need to achieve is a search query that selects a Candidate from the candidates table only if they have every Edit Qualifier that has been specified within the search.

My current attempt:

SELECT * FROM candidates LEFT JOIN candidates_edit_qualifiers_lookup ON candidates_edit_qualifiers_lookup.candidate = candidates.id WHERE  candidates_edit_qualifiers_lookup.edit_qualifier = '4' AND candidates_edit_qualifiers_lookup.edit_qualifier = '5'  GROUP BY candidates.id ORDER BY surname ASC, name ASC LIMIT 0, 20

I know that something is fundamentally wrong with this, but I don’t know if the solution lies in a different type of join or perhaps different table structure altogether?

Thanks in advance,

spitfireweb

SELECT candidates.* 
  FROM candidates 
INNER
  JOIN ( SELECT candidate
           FROM candidates_edit_qualifiers_lookup 
          WHERE edit_qualifier IN ('4','5') 
         GROUP 
             BY candidate
         HAVING COUNT(*) = 2 ) AS these
    ON these.candidate = candidates.id 
ORDER 
    BY surname ASC
     , name ASC 
LIMIT 0, 20

the number in the HAVING clause will be the number of listed edit qualifiers that they must have

Perfect. That’s exactly what I needed.

This is the best forum response I’ve ever had - exceptional.

Thank you very much,