I have this insert query:
insert into users_to_classes select ? , ?, 4
from (
select class_id, count(*) as students from users_to_classes where
participation_level=4 group by class_id
) as numUtC, classes where numUtC.class_id=classes.class_id and
classes.class_id=? and classes.spaces - ifnull(numUtC.students,0) > 0
That inserts users into the class if there are spaces available. This works if
and only if there is already a student user in the class. On a newly created
class the:
select class_id, count(*) as students from users_to_classes where
participation_level=4 group by class_id
subquery causes a null result.
What is the 'correct' way to handle something like this?
Aaron
ps. for short term functionality i changed it to:
select class_id, count(*) - 1 as students from users_to_classes group
by class_id
but I would like to allow for user types other than just teacher and student in
the future and only calculate enrollment based on students enrolled
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
