Limiting with a left outer join - Mailing list pgsql-general

I've been staring at this too long.  Could someone give me a bit of
sql help?


I want to show a list of upcoming workshops, and the number of
sessions (classes) offered for each workshop:

     workshop_id | classes_offered | review_mode | workshop_cat
    -------------+-----------------+-------------+--------------
               3 |               0 | t           | On Ground
              29 |               5 | f           | On Ground
              30 |               0 | f           | On Ground
              31 |               1 | f           | On Line
              61 |               3 | f           | On Ground
              62 |               2 | f           | On Ground
              63 |               1 | f           | On Line


A class is an instance of a given workshop (location and date given).
A class references a workshop.

Now, I'm using a LEFT OUTER JOIN to list workshops that don't have
any classes assigned yet.  Those are the zeros above.


Where I'm stuck is I need to apply limits to what rows to select.
For example, I don't want to include classes or workshops that are in
"review_mode".  Also, both workshops and classes can belong to
"domains" (via link tables) so need to only look at those, too.

Trying to do the "class_domain" join is where I'm stuck.  Here's
without that join, which sees to work:



    FROM        workshop w INNER JOIN workshop_category ON
                    (
                        workshop_category.id = w.workshop_category
                        AND w.review_mode IS FALSE
                    )

                INNER JOIN workshop_domain ON
                    (
                        workshop_domain.workshop = w.id
                        AND workshop_domain.domain = 1
                    )

                LEFT OUTER JOIN class c ON
                    (
                        c.workshop = w.id
                        AND c.register_cutoff_time >= now()
                        AND c.review_mode IS FALSE
                    )

The class table also has a "class_domain" table (like the
workshop_domain).  But, I'm not seeing how to make that join.


This pulls all the zeros out of the results:

                [...]

                LEFT OUTER JOIN class c ON
                    (
                        c.workshop = w.id
                        AND c.register_cutoff_time >= now()
                        AND c.review_mode IS FALSE
                    )
                    INNER JOIN class_domain ON (
                        class_domain.class = c.id
                        AND class_domain.domain = 1
                    )


It's these left outer joins that always get me.


What I think I need is something like:

            [...]

            LEFT OUTER JOIN class c ON
                (
                    c.workshop = w.id
                    AND c.register_cutoff_time >= now()
                    AND c.review_mode IS FALSE
                    AND class_domain.class = c.id
                    AND class_domain.domain = 1
                )

But, that's not part of the join, of course.

How do I make a join on the class table but not effect the left outer
join?


Thanks,



--
Bill Moseley
moseley@hank.org