Re: SQL Help: Multiple LEFT OUTER JOINs

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(at)hank(dot)org