SQL Help: Multiple LEFT OUTER JOINs - Mailing list pgsql-general

I need to generate a table of teachers, and the count of classes they
taught in the past and are scheduled to teach in the future.

 id  |  last_name   |   total    future_class_count | past_class_count
-----+--------------+----------+--------------------+-----------------
   3 |   Smith      |       12 |                  3 |               9
   8 |   Jones      |        0 |                  0 |               0


table person
    id
    last_name

table class
    id
    class_time

table role
    id
    role_name  -- for limiting to a type of teacher

-- link tables

table person_role
    person  references person
    role    references role

-- This table ties a person to a class, thus making them an instructor

table instructors
    person references person
    class references class


I can easily get instructors and the total count of their classes:

    SELECT      person.id AS id, last_name, count(instructors.class)

      FROM      person LEFT OUTER JOIN instructors ON (person.id = instructors.person),
                person_role

     WHERE      person_role.person = person.id
                AND person_role.role = 3  -- limit to this type of teacher

  GROUP BY      id, last_name;


Here's where I'm missing something.  Trying to do an outer join on
to bring in the class row with its class_time column:


    SELECT      person.id AS id, last_name,
                count(instructors.class) as total,
                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)) t
                     LEFT OUTER JOIN class on ( t.class = class.id ),
                person_role

     WHERE      person_role.person = person.id
                AND person_role.role = 3

  GROUP BY      person.id, last_name;





--
Bill Moseley
moseley@hank.org