Thread: Limiting with a left outer join
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
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 ... -- Michael Fuhr
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
On Sun, Feb 12, 2006 at 08:03:07AM -0800, Bill Moseley wrote: > 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. The usual way is to create a view that includes the columns you might want to restrict on, define the view to select all rows, then query the view with an appropriate WHERE clause. Another possibility is to create a set-returning function that accepts the relevant values as arguments and plugs them into the query. -- Michael Fuhr