The teacher for each of a student's classes writes a note against the student saying how they've performed throughout their time at college.
I need to pull out a report from the database (MySQL) showing who's written each note, and ideally, another showing how many each teacher has written.
Here's the query I've got at the moment:
select
concat(s.firstname,' ',s.lastname) as student,
p.content as reference,
concat(t.firstname,' ',t.lastname) as teacher
from
mdl_user as s,
mdl_post as p,
mdl_context as con,
mdl_role_assignments as ass,
mdl_user as t
where
s.id = p.userid and
p.courseid = con.instanceid and
con.id = ass.contextid and
ass.userid = t.id and
p.module='notes' and
p.created > unix_timestamp('2009-06-01')
order by
s.lastname;
(the date restriction is to omit any notes written before teachers started doing references). Unfortunately, this doesn't work as desired - currently, every note is linked to every teacher, returning about 6800 records (there are only around 300 references currently written). If anyone can help me with this, I'll be eternally grateful!