Re: SQL Help: Multiple LEFT OUTER JOINs

On Mon, Nov 21, 2005 at 05:40:10 -0800,
  Bill Moseley <moseley@hank.org> wrote:
>
> 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:

You don't say exactly why you are having a problem with this, but I think you
would be better off doing an inner join between instructors and class and
then do an outer join of that result to person.

>
>
>     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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly