Problem with MySQL (4.x) Multiple Table JOIN Query

Can't seem to get a 4 table query working the way I want

[mrwooley]

Hi!

I'm sure there is a simple or relatively simple solution to my multi-table query, but I can't seem to get my head around it. I've searched numerous forums but nothing has quite fit the bill. :\

I am trying to query the following tables to output all students (name, email, etc.) who have registered for a particular class date and time (unique code attached to each class date/time) and grouped by teacher name of selected class date/time.
Hope that makes sense!

In short, a roster of students for each instructor and his/her class.

I have four tables:

students ---
id (auto, primary)
first_name
last_name
email
status enum('active','inactive')

classes ---
code (unique alphanumeric code assigned to class title)
title
staus enum('current','archived')
description

dates ---
id (auto_increment, primary)
class (class code from 'classes' table)
code (unique date/time code for each class occurance)
start_date
end_date
instructor
location

registrations ---
id (auto_increment, primary)
class (class code same as 'classes' and 'dates' tables)
dates (unique date/time code same as 'code' field table 'dates')
student ('id' from table 'students')

Here is my working query and display block:

---- BEGIN CODE -----
$s = "SELECT r.student, r.dates,
u.id, u.last_name, u.first_name, u.email, u.title AS usertitle,
d.code, d.instructor,
c.code, c.title AS classname
FROM registrations AS r
LEFT JOIN students AS u ON (r.user = u.id)
LEFT JOIN dates AS d ON (r.session = d.code)
LEFT JOIN classes AS c ON (r.course = c.code)
WHERE r.status = 'active' AND r.dates = d.code AND d.instructor = 'Mr. T'
ORDER BY d.class ASC, u.last_name ASC";
$r = mysql_query( $s ) or die( "Error with MySQL:<br>" . $s . "<br>" . mysql_error() );
while ($row = mysql_fetch_array($r)) {
$userlast = $row['last_name'] ;
$userfirst = $row['first_name'] ;
$usertitle = $row['usertitle'] ;
$email = $row['email'] ;
$course = $row['code'] ;
$id = $row['id'] ;
$title = $row['classname'] ;

$display_block .= "

<tr style=\"color:#000000\">
<td><strong>$userlast, $userfirst</strong></td>
<td>$usertitle</td>
<td><a href=\"mailto:$email\">$email</a></td>
<td>$location</td>
<td>$title</td>
</tr>" ;

----- END CODE -----

This gets ALL students taught by Mr. T for that particular class but not by date. Since classes have multiple dates taught by the same instructor, I need these split out.

I've tried different joins to pull in the 'instructor' field to no avail.

So a couple of questions:
How should I add a variable for 'instructor' for this query?
How can I limit the results to just one class and one date/time per instructor -- not pull all students for a particular class/instructor?

Do I need to create a tmp table?

(Sorry I'm pulling some of my PHP into this Database forum and for the long post!)

I'm frustrated. It's just a class roster!
Thanks in advance for any and all help!

M