Mysql Select table records which have not been updated since last month I have a table names 'scoredata' having fields
id, lessonid, levelid, studentid, addedate
in which score for each day will be entered by the admin.
I want to select only those studentid whose score has not been
added from last four weeks. How can I do this in a single query.
SELECT studentid FROM
(
    SELECT MAX(addedate) lastdate,studentid
    FROM scoredata GROUP BY studentid
) A
WHERE lastdate &lt; ( DATE(NOW()) - INTERVAL 4 WEEK );


Query Breakdown


The subquery gets the last date each student entered a score
The WHERE clause outside the subquery checks to all lastdate values older than 4 weeks


Make sure you index studentid,addedate. If you didn't then run

ALTER TABLE scoredata ADD INDEX studentid_addedate_ndx (studentid,addedate);


Give it a Try !!!