Mysql连接四个表并显示NULL值-MysqljoinfourtablesandshowNULLvalue

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....

我需要进行查询以获得每个学生的特定考试的结果,并且我还想为没有输入他的分数的学生显示NULL。

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)

带有Student表的分数表(user_id)(user_id)

• Exam table ( id ) with scores table ( exam.id)

考试表(id)与分数表(exam.id)

• student_subject ( user_id ) with student table ( user_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)

  student_subject(group_id)与考试表(group_id)(由于某些重要原因,我的组数据库在另一个数据库上)

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

我的查询有效,但对于没有输入分数的学生,我没有NULL值

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 ) ?

如何为每个没有特定考试成绩的学生(exam.id = 32)获取空值?

Edited for @sceaj

编辑@sceaj

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

我得到了你的查询(我更改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

2 个解决方案

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

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

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