On Apr 5, 2011, at 10:42 AM, Jennifer Wollesen wrote:
> select
> *
> from
> users_to_classes
> left join classes on (users_to_classes.class_id = classes.class_id)
> left join users on (users_to_classes.user_id = users.user_id)
> left join (
> select
> class_id, count(*) as students
> from
> users_to_classes
> where
> participation_level = 4
> group by
> class_id
> ) as numUtC on users_to_classes.class_id = numUtC.class_id
> where
> users_to_classes.participation_level = 2
oh wow, that is MUCH cleaner than what I frankensteined together last night.
That is quite excellent.
Quick question about your "left join" statement. The only "missing data" will
be that classes won't have students (no p_level=4 rows in the u_to_c table)
until after a student joins. The final looks like this:
select
users.user_id, classes.class_id, name, spaces-ifnull(students,0) as
openings, fname, lname
from
users_to_classes
join classes on (users_to_classes.class_id = classes.class_id)
join users on (users_to_classes.user_id = users.user_id)
left join (
select
class_id, count(*) as students
from
users_to_classes
where
participation_level = 4
group by
class_id
) as numUtC on users_to_classes.class_id = numUtC.class_id
where
users_to_classes.participation_level = 2
order by name
which is a million times better than what I put together last night.
Thanks again
Aaron
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
