On Apr 6, 2011, at 1:08 AM, Aaron Luman wrote: > 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?
Try using a left join: insert into users_to_classes select ? , ?, 4 from classes left join ( select class_id, count(*) as students from users_to_classes where participation_level=4 group by class_id ) as numUtC on numUtC.class_id=classes.class_id where classes.class_id=? and classes.spaces - ifnull(numUtC.students,0) > 0 Or a subselect within the ifnull: insert into users_to_classes select ? , ?, 4 from classes where classes.class_id=? and classes.spaces - ifnull((select count(*) from users_to_classes where participation_level=4 and class_id=classes.class_id),0) > 0 The key to both approaches is you avoid the inner join, which is what caused it not to work when there were no rows. Jon _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
