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@xxxxxxxx
