I am trying to do this in one query. I asked a similar question a few days ago but my personal requirements have changed.

I have a game type website where users can attend "classes". There are three tables in my DB.

I am using MySQL. I have four tables:

• hl_classes (int id, int professor, varchar class, text description)
• hl_classes_lessons (int id, int class_id, varchar lessonTitle, varchar lexiconLink, text lessonData)
• hl_classes_answers (int id, int lesson_id, int student, text submit_answer, int percent)

hl_classes stores all of the classes on the website.

The lessons are the individual lessons for each class. A class can have infinite lessons. Each lesson is available in a specific term.

hl_classes_terms stores a list of all the terms and the current term has the field active = '1'.

When a user submits their answers to a lesson it is stored in hl_classes_answers. A user can only answer each lesson once. Lessons have to be answered sequentially. All users attend all "classes".

What I am trying to do is grab the next lesson for each user to do in each class. When the users start they are in term 1. When they complete all 10 lessons in each class they move on to term 2. When they finish lesson 20 for each class they move on to term 3. Let's say we know the term the user is in by the PHP variable $term.

So this is my query I am currently trying to massage out but it doesn't work. Specifically because of the hC.id is unknown in the WHERE clause

SELECT hC.id, hC.class, (SELECT MIN(output.id) as nextLessonID
  FROM ( SELECT id, class_id
   FROM hl_classes_lessons hL
   WHERE hL.class_id = hC.id
   ORDER BY hL.id
   LIMIT $term,10 ) as output
  WHERE output.id NOT IN (SELECT lesson_id FROM hl_classes_answers WHERE student = $USER_ID)) as nextLessonID
FROM hl_classes hC

My logic behind this query is first to For each class; select all of the lessons in the term the current user is in. From this sort out the lessons the user has already done and grab the MINIMUM id of the lessons yet to be done. This will be the lesson the user has to do.

I hope I have made my question clear enough.