Artificial Intelligence using sql query/stored procedure -

i'm making application should organize employees jobs.

one employee can't in 2 jobs simultaneously.
every employee has 1 or more qualifications.
every job has 1 or more qualifications required job.
* of jobs should staffed.

these tables:
1. employees table - every person has pid number.
2. qualifications table - [id, name].
3. qualificationsofemployee - every person can have more 1 qualification [pid, qualificationid].
4. jobs [id, name, numofemployee(how people needed job)]
5. qualificationsrequireforjob table - [jobid, qualificationid]

now, make query/function find qualified employee/s (depend on jobs.numofemployee) every job. trick there more 1 qualified employee every job, of them maybe needed other jobs (cause have many qualifications).

i don't think simple sql join or sub-query work.
there ideas?

thanks lot, elad

mysql may not best language type of algorithm, it's possible guess.

you can (not way) implement genetic algorithm. these type of algorithms used try find best combination given problem , prevents bruteforce test possibilities. coupled powerufll cost function, allow find great solution (may not best). cost function, in case may calculated number of affected employees, jobs without employees, , number of qualifications of affected employe (set employe 45 qualifications may not best solution if job requiers 1 qualif , other employees have qualification). diffuculty can find best cost function.

"genetic algorithm assignment" on google may give papers different algorithms can fit problem.

find best combination in assignement problem can cpu expensive, , long executed. depends on algorithm , set of data.

good luck these if you're going way !