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
LEFT JOIN user_subject ON user.uid=user_subject.uid
LEFT JOIN assignment ON user_subject.uid=assignment.uid