Re: Limiting with a left outer join

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(at)hank(dot)org