MySQL-help needed

[lavik]

Hi!
I have som problems finding the right sql-query for outputting data from a MySQL-db.

Let's say I have 4 tables.I have 4 tables(actually there are more, but I think only four are of interest here):

user: uid, username, firstname, lastname
assignment: aid, filename, uid
subject: sid, subjectname
user_subject: uid, subject

What I need is to list out all the users, those who have delivered an assignment should have the filename listed next to their name. Others will instead have the text 'no file'.

I've tried alot, and this is what I've come up with, but it's not correct. I could use some help from others...

 
$sql = "SELECT DISTINCT user.firstname, user.lastname, IFNULL(assignment.filename, 'no file') AS filename
	  FROM user 
	  LEFT JOIN user_subject ON user.uid=user_subject.uid 
	  LEFT JOIN assignment ON user_subject.uid=assignment.uid 
	  WHERE user_subject.subjectname='english' 
	  AND assignment.aid='1'"; 

[nickc]

What does it do as it stands?

Nick

[redemption]

Hmm this is a double post.

Please follow up your comments here -> http://www.sitepointforums.com/showt...hreadid=106326 . Check out post #8.

Thanks!