Round-robin selection in MySQL

I have below tables:

class_student_mapping

class_id   student_id
    1   1
    1   2
    1   3
    1   4
    2   1
    2   2

student_program_mapping

student_id     class_id   program_id
    1               1         1
    2               1         1

I need to assign programs to student by class basis.

I will get class id and program id, then I need to check the latest assigned student in student_program_mapping after getting that student I will assign new student from class_student_mapping table which has student_id greater than last assigned student.

For this I am using below SQL:

SELECT MAX( `student_id` ) as last_assigned_student
FROM `student_program_mapping`
WHERE `class_id` =1
AND `program_id` =1

Which gives me result = 2 Now i have find in class_student_mapping table student id which is just greater than 2

SELECT `student_id`
FROM `class_student_mapping`
WHERE `student_id` > (
SELECT MAX( `student_id` ) AS last_assigned_student
FROM `student_program_mapping`
WHERE `class_id` =1
AND `program_id` =1 ) order by student_id  limit 1

I gives me student id as 3 which is my desired result

But I have issue if I have to do it for class id 2 which does not have any student program mapping in student program mapping table than how to get student id in this case.

As in case of class 2, I need to add student first time in student_program_mapping table so I require the lowest student id but with this query I am not getting it for first time.

Answer

Your problem is that the subquery is returning NULL because there are no entries for class_id=2 in student_program_mapping, and any comparison with NULL returns false. To fix it, wrap the MAX(student_id) in an IFNULL so that it returns 0 for that case:

SELECT `student_id`
FROM `class_student_mapping`
WHERE `student_id` > (
SELECT IFNULL(MAX( `student_id` ), 0) AS last_assigned_student
FROM `student_program_mapping`
WHERE `class_id` =2
AND `program_id` =1 ) order by student_id  limit 1

Output (SQLFiddle):

student_id
1