Mysql joins four tables and shows null value
I need to make a query to get the result of a specific exam for each student, and I also want to display NULL for a student who was not included in his results ....
Here are my four tables
-
Student
table -
Scores
table -
Student
-
student_subject
My table is link
- Score table (user_id) with Student table (user_id)
- Exam table (id) with grades table (exam.id)
-
student_subject (user_id) with student table (user_id)
-
student_subject (group_id) with exams table (group_id) (my main database is in a different database for some important reason)
My query works, but I don't have a NULL value for a student who hasn't entered their account
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 zero for each student who does not have a grade for a particular exam (exam.id = 32)?
Edited for @sceaj
I got this with your request (I change WHERE exam.id = 34 to 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
source to share
The following is to return all students with exam scores of 32, if any.
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 it was for this? Perhaps you can start from the above and build from there.
Edit: The new strategy is based on my first comment. Try to find all students, then find the grades found, null if missing.
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
source to share
Don't start with the scoring table, leaving the connection with everyone else: you only get rows where the score is not zero. Instead, make the correct join, or put the grading 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
source to share