On Mon, Nov 21, 2005 at 02:06:35PM -0800, Bill Moseley wrote:
> The now working query (thanks to you!) is:
No that doesn't work. It's dropping the people that have never
been assigned a class to teach (i.e. don't have a row in the
"instructors" link table).
> FROM class INNER JOIN instructors ON class.id = instructors.class
> LEFT OUTER JOIN person ON person.id = instructors.person,
> person_role
I really seem to need the multiple left outer join. This works:
SELECT person.id AS id, last_name,
person_role.role AS role,
count(instructors.class),
sum (CASE WHEN class_time > now() THEN 1 ELSE 0 END) as future_class_count,
sum (CASE WHEN class_time <= now() THEN 1 ELSE 0 END) as past_class_count
FROM person LEFT OUTER JOIN instructors ON (person.id = instructors.person)
LEFT OUTER JOIN class ON (instructors.class = class.id),
person_role
WHERE person_role.person = person.id
-- AND person_role.role = 2
GROUP BY person.id, last_name, person_role.role;
I'm not clear how to move that "person_role.person = person.id" into
the FROM statement. Does it matter?
--
Bill Moseley
moseley@hank.org