[UPHPU] How to handle a null subquery in MySQL

Jon Jensen jenseng at gmail.com
Wed Apr 6 09:26:33 MDT 2011


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


More information about the UPHPU mailing list