On Mon, Nov 21, 2005 at 05:40:10 -0800, Bill Moseley <moseley@xxxxxxxx> wrote: > > Here's where I'm missing something. Trying to do an outer join on > to bring in the class row with its class_time column: You don't say exactly why you are having a problem with this, but I think you would be better off doing an inner join between instructors and class and then do an outer join of that result to person. > > > SELECT person.id AS id, last_name, > count(instructors.class) as total, > sum (CASE WHEN class_time > now() THEN 1 ELSE 0 END) as future_class_count, > sum (CASE WHEN class_time <= now() THEN 1 ELSE 0 END) as past_class_count > > > FROM (person LEFT OUTER JOIN instructors ON (person.id = instructors.person)) t > LEFT OUTER JOIN class on ( t.class = class.id ), > person_role > > WHERE person_role.person = person.id > AND person_role.role = 3 > > GROUP BY person.id, last_name; > > > > > > -- > Bill Moseley > moseley@xxxxxxxx > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly
