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,
JOIN ( SELECT candidate
WHERE edit_qualifier IN ('4','5')
HAVING COUNT(*) = 2 ) AS these
ON these.candidate = candidates.id
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,