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