Re: More schema design advice requested

On Mon, Oct 13, 2008 at 04:29:45PM +0000, Matthew Wilson wrote:
> I track employee qualifications in one table and I track job
> requirements in another table. A job requires zero-to-many
> qualifications, and for an employee to be qualified for that job, the
> employee must have ALL the requirements.
>
> I want to find all jobs that employee #2 is qualified for

I think you want to use an "outer join". Maybe something like:

SELECT r.job_id, bool_and(q.employee_id IS NOT NULL) AS is_qualified
FROM job_requirement r
LEFT JOIN employee_qualification q
ON q.requirement_id = r.requirement_id AND
q.employee_id = 2
GROUP BY r.job_id;

If you want to extend this to more than one employee you'd need to do
something like:

SELECT e.employee_id, r.job_id
FROM employees e, job_requirement r
LEFT JOIN employee_qualification q
ON q.requirement_id = r.requirement_id AND
q.employee_id = e.employee_id
WHERE e.employee_id IN (2,3,4)
GROUP BY e.employee_id, r.job_id
HAVING bool_and(q.employee_id IS NOT NULL);

I.e. get the cross product of all employees and jobs, match them up to
what they're qualified for. Moving the "is_qualified" expression down
into the HAVING clause causes the query to only return jobs for which
the employee is fully qualified for.

Sam