mysql-php querying a list to build a table

Question

I've had the same problem with sql for a while and as I sit down to write the same 'brute-force' hack I always use, I figure there MUST be a more efficient way to do what I want to do.

I have tables similar to this:

grades(gradeID, taskID, grade, username, date)
tasks(taskID, task...)
assignment(assignmentID, title...)
assignment_tasks(assignmentID, taskID)
assignment_students(assignmentID, username)
students(username, forename...)

the last 5 tables are pretty static, set up once and mostly left alone.

for the grades table, a new record is created every time a new grade is entered for a task.

I want to produce a summary table for an assignment, which might consist of say, 5 tasks and each student may have any number of grades for each task, with the most recent one being the one I want to display in the summary.

What I'd normally do is query a list of students, and a list of assignment tasks, then build a giant nested for loop looping through each task for each student, querying the most recent grade for each, so assuming 30 students, 5 tasks, that's 152 queries, which has always struck me as waaay too many.

I figure (hope) I have a horribly embarrassing gap in my sql knowledge and there's a much cleverer way to do it.

Thanks -Malphas

Edit: Thanks for the answer - I'm still working on building the actual database so I can test it, but I suspect that the answer below doesn't cover the following issues:

if a student hasn't attempted a task/assignment, there won't be any entries for them in the grades table but they still need to show in the summary table with a default grade for each task ("u"). I think this bit makes it harder.

Edit again: I have the embryo database now and it works in that I get a list of most recent grades with gaps where there's no grade. Transposing that list is now the subject of another question!

Answer 1

SELECT  tasks.*, students.*,
        (
        SELECT  grade
        FROM    grades
        WHERE   grades.task_id = tasks.task_id
                AND grades.username = students.username
        ORDER BY
                date DESC
        LIMIT 1
        ) AS lastgrade
FROM    assignments a
JOIN    assignment_tasks at
ON      at.assignmentID = a.assignmentID
JOIN    assignment_students ast
ON      ast.assignmentID = a.assignmentID
JOIN    tasks
ON      tasks.task_id = at.task_id
JOIN    students
ON      students.username = ast.username