Reputation: 3295
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.
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!
Upvotes: 1
Views: 128
Reputation: 425803
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
Upvotes: 1