I've got a database which defines job candidates and jobs. Each
candidate has a set of skills each with a ability score and a
experience score. Each job has a required skillset.

I need to match these two sets by returning the candidates which best
match a job's required skillset based on both the number of relevant
skills they have and their scores in those skills.

The part I'm having trouble with is returning candidates that don't
have all the skills. There's a number of ways of achieving this
programmatically (it's part of a PHP application) or via a stored
procedure, but is there a simpler technique I'm overlooking?

Any help would be appreciated.

What sort of joins are you doing?

battle.chris (AT) gmail (DOT) com wrote:

battle.chris (AT) gmail (DOT) com wrote:
I guess your structure looks something like this (If it doesn't, I
think it should!)

candidates(candidate_id*,candidate)
skills(skill_id*,skill)
jobs(job_id*,job)
candidates_skills(candidate_id*,skill_id*)
jobs_skills(job_id*,skill_id*)

Then I guess your query could look like this (untested)

SELECT candidate_id from jobs_skills WHERE job_id = x
LEFT JOIN candidates_skills USING skill_id

You could then use the results of this query as the derived table of a
subquery, allowing you to order by most suitable candidates for
instance.