Mysql join four tables and show NULL value

问题:

I need to do a query to get the result of an specific exam for each student , and I also want to display NULL for student who didn't enter his scores....

Here is my four table

1. Student table
2. Scores table
3. Student
4. student_subject

My table link

• Score table (user_id) with Student table (user_id)
• Exam table ( id ) with scores table ( exam.id)

• student_subject ( user_id ) with student table ( user_id)

• student_subject (group_id) with exam table (group_id) (my group database is on another database for some important reason)

My query works, but I don't have a NULL value for the student who don't have enter his score

SELECT 
    scores.result, students.id, exam.name, exam.id
FROM 
    scores
LEFT JOIN  
    students ON scores.user_id = students.user_id
LEFT JOIN 
    exam ON exam.id = scores.exam_id
LEFT JOIN 
    students_subjects as ss ON ss.user_id = students.id
LEFT JOIN 
    students_subjects ON students_subjects.group_id = exam.group_id
WHERE
    exam.id = 32
GROUP BY
    scores.id

Output

How can I get a null value for each student who don't have score for a specific exam ( exam.id = 32 ) ?

Edited for @sceaj

I got this with you're query ( I change WHERE exam.id = 34 for WHERE exam.id = 36 for better testing)

SELECT scores.result,students.id,exam.name, exam.id
FROM exam
INNER JOIN students_subjects ON students_subjects.group_id = exam.group_id
INNER JOIN students ON students_subjects.user_id = students.user_id
LEFT OUTER JOIN scores ON scores.user_id = students.user_id
WHERE exam.id = 36

回答1:

The following should return all students, with scores from exam 32 if they exist.

SELECT scores.result,students.id,exam.name, exam.id
FROM exam
INNER JOIN scores ON exam.id = scores.exam_id
RIGHT OUTER JOIN students ON scores.user_id = students.user_id
WHERE exam.id = 32

Your select clause didn't use anything from students_subjects or aggregate functions, so I'm not sure what the join to it was for? Perhaps you can start with the above and build from there.

Edit: New strategy based on my first comment. Try and locate all students then find scores that exist, null if none.

SELECT scores.result,students.id,exam.name, exam.id
FROM exam
INNER JOIN students_subjects ON students_subjects.group_id = exam.group_id
INNER JOIN students ON students_subjects.user_id = students.user_id
LEFT OUTER JOIN scores ON scores.user_id = students.user_id
  AND scores.exam_id = exam.id
WHERE exam.id = 32

回答2:

Don't begin with the score table, left joining all the others: you only will get lines where score is not null. Instead, do a right join, or put the scores table at the end:

SELECT scores.result,students.id,exam.name, exam.id
FROM 
students LEFT JOIN scores ON scores.user_id = students.user_id
LEFT  JOIN exam on exam.id = scores.exam_id
LEFT JOIN students_subjects as ss ON ss.user_id = students.id
LEFT JOIN students_subjects ON students_subjects.group_id = exam.group_id
where exam.id = 32
group by scores.id