Re: SQL Help: Multiple LEFT OUTER JOINs

On Mon, Nov 21, 2005 at 05:40:10 -0800,
Bill Moseley <moseley(at)hank(dot)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(at)hank(dot)org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly