Is it possible to dynamically change the where clause as a query executes based on the value of one of the columns? That is, lets say (and this is a completely made up example) I have a table of students, classes attended, and if they were tardy. I want to see for each student a list of all classes they have attended since the last time they were tardy. So the query would look something like this:
SELECT student, class, classdate
FROM attendance
WHERE classdate>(<<SOME QUERY that selects the most recent tardy for each student>>)
ORDER BY student,classdate;
or, to put it into more programing terminology, to perhaps make it clearer:
for studentName in (SELECT distinct(student) FROM attendance):
SELECT student, class, classdate
FROM attendance
WHERE classdate>(SELECT classdate
FROM attendance
WHERE tardy=true AND student=studentName
ORDER BY classdate DESC
LIMIT 1)
ORDER BY classdate;
Is there any way to do this with a single query, or do I need to do a separate query for each student (essentially as per the loop above)? The actual use case is more complicated to explain (it has to do with certifying records, and which ones need to be looked at) but conceptually it is the same.
