Hi there,
Been working on an evolving DB program for a while now. Suddenly I have come
across a situation where I need to update a table based on a group by query.
For example, I have a table called "students". I need to update a field
called "status" on this table for all members that have never attended a
class.
Class attendance is recorded by another table (which represents the many to
many relationship between a student and a class) called "studentclassli nk"
which has fields, student_id, class_id as well as it's own primary key,
"studentclassli nk_id".
Now how would I go about writing a query that updates the "status" field
only for students that have attended no classes?
At first I thought this was easy as I can display a count of how many
classes a student has attended by a query like so:
SELECT students.studen t_id, students.studen t_name, count(class_id) as
classcount
FROM students
LEFT JOIN `studentclassli nk` ON students.studen t_id =
studentclasslin k.student_id,
GROUP BY students.studen t_id
This will give me a list of each student and how many classes they have
attended. I want to update all the students who have not attended any
classes (therefore a classcount of 0).
Bear in mind that my host is running MySQL version 3.23.
Kind regards,
Dave