mysql and joins

Draco

I have three tables:
users (user_id, username)
classlists (user_id, module_id)
submissions (user_id, module_id, assignment_id, submission_id)

I am trying to get an end result where I have a table of all the users in a module and their submission for an assignment. If there is no submission, I want nulls instead.
ie
user_id | module_id | assignment_id | submission_id
1 | 1 | 1 | 1
2 | 1 | 1 | 2
3 | 1 | null | null

I'm using the following query:

SELECT * FROM 
(classlists INNER JOIN users ON (classlists.user_id = users.user_id))
 LEFT JOIN 
submissions ON (users.user_id = submissions.user_id) 
WHERE
 (assignment_id = 1 OR assignment_id IS NULL) AND classlists.module_id = 1

Instead of getting what I want above, I am just getting rows with all the info in them.

am I going the right direction? or just barking up the wrong tree?

amen

try this

Select sub.* From
classLists cs Left outer join submissions sub on (cs.userid =sub.userid)
or am I missing something ?

the other thing is in classlists you should really have a ClassListsID and use that in the submissions table (normalisation and all that jazz)