Hi,

I have 2 tables in my db: students and absences. In students: id_student, s_name; and in absences: id_student, date, day(the day of the week as number-it's not so important); I would like to get the students that were absent for more than 2 days consecutive and the dates.

At the moment I have the next query:

 /*To control when the days are in the same week*/  
SELECT id_student, date, dayname(date),date_add(date, INTERVAL 1 DAY) AS a,  
 dayname(date_add(date, INTERVAL 1 DAY)) AS an, date_add(date, INTERVAL 2 DAY) AS b,  
 dayname(date_add(date, INTERVAL 2 DAY)) AS bn  
FROM absences AS a  
WHERE id_student IN  
                (SELECT id_student FROM absences   
                  WHERE id_student = a.id_student  
                  AND date = date_add(a.date, INTERVAL 1 DAY) )  
AND id_student IN  
               (SELECT id_student FROM absences  
                 WHERE id_student = a.id_student  
                 AND date = date_add(a.date,INTERVAL 2 DAY) )  
UNION  
/*To control when the days jump to the next week*/  
SELECT id_student, date, dayname(date),date_add(date, INTERVAL 3 DAY) AS a,  
 dayname(date_add(date, INTERVAL 3 DAY)) AS an, date_add(date, INTERVAL 4 DAY) AS b,  
 dayname(date_add(date, INTERVAL 4 DAY)) AS bn  
FROM absences AS a  
WHERE id_student IN  
               (SELECT id_student FROM absences  
                 WHERE id_student = a.id_student  
                  AND date = date_add(a.date, INTERVAL 3 DAY) )  
AND id_student IN  
                (SELECT id_student FROM absences  
                  WHERE id_student = a.id_student  
                  AND date = date_add(a.date,INTERVAL 4 DAY) )  
/* To avoid the case (Monday-Thursday-Friday) to be consider as consecutive days*/  
AND WEEKDAY(date) !=0   

Thanks