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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq