Query help... - Mailing list pgsql-general

A simplified version of my situation...

I've got three tables that I'm working with... the first is a table of
staff members... firstname, lastname, staffid... nothing tough... the
second is a table of jobs... job title, jobid... nothing tough... the
third is a history of job assignments... it's got a staffid, a jobid, a
timestamp, and a field to denote whether the job was added or dropped...
this third table exists because it is useful in this project to be able
to look at a staff member's job history...

The problem I'm running into is queries that attempt to determine who is
an "active" member of the staff, defined as a staff member who currently
has at least one job added but not dropped... this can be easily
measured by saying that if the number of times a staff member had a job
added is greater than the number of times that same staff member had a
job dropped, that staff member is active...

What I'm having trouble figuring out is how can I incorporate those
rowcounts into a greater query...

I've succeeded in some other cases... I can get a list of the active
personnel:

select staff.staff_id, firstnames, lastname, jobs.job_id, job_title from
staff, staff_history, jobs where staff.staff_id = staff+history.staff_id
and jobs.job_id = staff_history.job_id group by staff.staff_id,
firstnames, lastname, jobs.job_id, job_title having (count(add_or_drop)
% 2) = 1 order by lastname, firstnames;

This succeeds because for each person-job combination, if there's 0 mod
2 instances of them, then the person has added and dropped the job (some
number of times)... if there's 1 mod 2 instances, then the person has
added but not dropped the job...

I can get a list of jobs and who is actively assigned to the job:

select jobs.job_id, job_title, staff.staff_id, firstnames, lastname from
jobs left outer join (staff_history join staff on
(staff_history.staff_id = staff.staff_id)) on (jobs.job_id =
staff_history.job_id) group by jobs.job_id, staff.staff_id, job_title,
firstnames, lastname having (count(staff.staff_id) % 2 = 1);

This succeeds because for each grouping, which again is a person-job
combination, I can eliminate anybody who has been assigned and
deassigned to the same job the same number of times...

But here's a case where I can't figure it out... I'm trying to generate
a list of staff members with the "active" staff members first... now, I
can't use my same of mod 2 here, because a person can have more than one
job... so if i have been added to two jobs but not removed from any,
count(add_or_drop) % 2 = 0, but I am active...

Any suggestions?

-jag


--
--------------------------------------------------------
Joshua Ginsberg                 rainman@owlnet.rice.edu
Director of Technology          dirtech@sa.rice.edu
Student Association             AIM: L0stInTheDesert
Rice University, Houston, TX
========================================================
"Programming today is a race between software engineers
striving to build bigger and better idiot-proof programs
and the Universe trying to produce bigger and better
idiots. So far, the Universe is winning." -Rich Cook
--------------------------------------------------------