Find two continuous dates

I have thee tables:

  1. lessons:
    lesson_id, date
  2. participation_tracking:
  3. dates of lessons the student should come

I need to find two things:

  1. students that were missing in X lessons
  2. students thet were missing in X continuous lessons

The first part was easy, and I made it like this:

SELECT (count(distinct lessons.lesson_id)- count(distinct participent_tracking.lesson)) as missed_lessons , students.student FROM (students_lessons join lessons on ( left outer join participent_tracking on (participent_tracking.student=students_lessons.student and participent_tracking.lesson=lessons.lesson_id)

But as for the second part…If I could use lead\lag in MYSQL it would be possible, but I don’t have any other idea…

I think you’ll have to get all lessons a student should attend from the database and left join all attended lessons to them, and then loop through the result set with PHP (or whatever language you’re using).

sigh, if only the homework assignment allowed that, because that’s the most sensible solution, isn’t it