Take the 2-minute tour ×

I have 3 tables


# id
* max_students


# id
* school


# id
* date_id
* over_limit
* canceled - DEFAULT NULL
* logged_out - DEFAULT NULL

When user register a student, I use query to count registered students, if number of students aren't higher or equal to max_students in his/her school. This result (0 or 1) later save to over_limit cell

I use this query to count

  (SELECT SC.max_students
  FROM schools SC 
  JOIN dates D ON D.school = SC.id
  WHERE D.id = S.date_id)
FROM students S
WHERE S.date_id = "number from PHP" AND S.logged_out IS NULL AND S.canceled IS NULL

But there is something bad, because sometimes student was marked as over_limit even, when date wasn't full, sometimes when date was full, student wasn't marked as over_limit.

So I decided to log these results. I save result of the query above, COUNT and max_students selected in new query. In 177 occurrences, 9 times query returned bad result.

Can you explain what is wrong in my query?

Thank you

share|improve this question
Are you certain that you want the conditions S.logged_out IS NULL AND S.canceled IS NULL? What happens if they change after the fact? –  eggyal Oct 14 '12 at 11:07
Yes, logged_out is INT, NULL by default, and when user log out the student from course (table dates), I change this to 0 if no alternate student is registred, or ID of student which is alternate. canceled is VARCHAR, NULL by default. When admin cancel registration of this student, I put there reason why he did it. –  Arxeiss Oct 14 '12 at 11:28
And users never log back in / "uncancel" their registration? If so, this could be concurrency-related... are you using transactions? –  eggyal Oct 14 '12 at 11:29
I don't understand first question. But yes, I use transactions. –  Arxeiss Oct 14 '12 at 11:50

Your Answer



Browse other questions tagged or ask your own question.