I wish to extract a class list of students with assessment details on for a given term. The report must list all of the student names for the specified group, along with the assessment information for that term if it exists, otherwise the assessment part should be blank.

I am using the following query:

SELECT s.AdmissionNo, s.Surname, s.Forename, t.Progress, t.Effort, t.Behaviour, t.Classwork, t.Homework FROM Lessons l
INNER JOIN Students s ON l.AdmissionNo=s.AdmissionNo
LEFT OUTER JOIN Reports_Termly t ON t.LessonID=l.LessonID
WHERE l.GroupRef="mat/09/MS-M1" AND t.TermID="3"

The table definitions are as follows:

CREATE TABLE `students` (
`AdmissionNo` int(11) NOT NULL default '0',
`Surname` varchar(30) NOT NULL default '',
`Forename` varchar(30) NOT NULL default '',
PRIMARY KEY (`AdmissionNo`));

CREATE TABLE `lessons` (
`LessonID` int(10) unsigned NOT NULL auto_increment,
`GroupRef` varchar(20) NOT NULL default '',
`AdmissionNo` int(11) NOT NULL default '0',
PRIMARY KEY (`LessonID`));

CREATE TABLE `reports_termly` (
`LessonID` int(10) unsigned NOT NULL default '0',
`TermID` int(10) unsigned NOT NULL default '0',
`Progress` char(1) NOT NULL default '',
`Effort` tinyint(1) unsigned NOT NULL default '0',
`Behaviour` tinyint(1) unsigned NOT NULL default '0',
`Classwork` tinyint(1) unsigned NOT NULL default '0',
`Homework` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`LessonID`,`TermID`));

However, the query mentioned above does not return the students in the group that do not have any assessment details for this term (which I require it to do).

If anyone has any suggestions on how to do this, please let me know.

Thanks in advance

Chris