Posted by Dave Smithz on 01/22/06 22:43

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 "studentclasslink"
which has fields, student_id, class_id as well as it's own primary key,
"studentclasslink_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.student_id, students.student_name, count(class_id) as
classcount
FROM students
LEFT JOIN `studentclasslink` ON students.student_id =
studentclasslink.student_id,
GROUP BY students.student_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

• Next in forum: Re: Motivation for reporting PHP bugs
• Prev in forum: Re: Two for { }
• Thread view: Update query needed - on a "group by" query - Possible / How???

[Reply to this message]