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

On Sat, Feb 11, 2006 at 12:35:34AM -0700, Michael Fuhr wrote:
> On Fri, Feb 10, 2006 at 11:59:30AM -0800, Bill Moseley wrote:
> > How do I make a join on the class table but not effect the left outer
> > join?
>
> Are you looking for something like this?
>
> LEFT OUTER JOIN (class INNER JOIN class_domain ON ...) c ON ...

Why, yes I am.  Thank you.

I'll post my select below, just in case anyone cares to review it for
sanity. ;)



Something is not quite right about my schema, I fear.  The idea of the
domains is to limit viewing of classes and workshops to different
groups of users.  A given workshop may be available to more than one
group.

But, one problem is it's possible that a class and its parent
workshop may not have a domain in common.  Maybe that's something the
application code needs to enforce, and not the database.



BTW -- Is there a way to turn something like this into a view?  The
2 domain bind parameters will alway match, and the only other
input parameters are the two review mode booleans.  That is, the
input to the query is a domain id, and if "review_mode" must be false.


SELECT      w.id,
            count(c.id) as class_count,
            w.name as name,
            scheduled_message,
            no_scheduled_message,

            (CASE
                WHEN workshop_comment_end_time > now()
                THEN workshop_comment
                ELSE NULL
            END) AS workshop_comment,

            (CASE
                WHEN new_workshop_end_time > now()
                THEN '1'
                ELSE NULL
            END) AS is_new,

            w.review_mode as review_mode,

            workshop_category.name as workshop_cat



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 = ?
                )

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




GROUP BY    1,3,4,5,6,7,8,9,
            w.start_display_time,
            w.stop_display_time


HAVING      ( count(c.id) > 0 ) OR
            (
              (now() between w.start_display_time and w.stop_display_time)
              OR
              (w.stop_display_time IS NULL AND
                -- probably don't need to check for NOT NULL here
                w.start_display_time IS NOT NULL AND w.start_display_time <= now())
              OR
              (w.start_display_time IS NULL AND
                w.stop_display_time IS NOT NULL and w.stop_display_time > now())
            )

ORDER BY    w.id


--
Bill Moseley
moseley@hank.org