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,