Students and Examinations _ Easy. Leetcode *

 

Solution

SELECT st.student_id, 
    st.student_name, 
    su.subject_name, 
    COUNT(ex.student_id) AS attended_exams
FROM Students st
CROSS JOIN Subjects su
LEFT JOIN Examinations ex
ON st.student_id = ex.student_id AND su.subject_name = ex.subject_name
GROUP BY st.student_id, st.student_name, su.subject_name
ORDER BY st.student_id ASC, su.subject_name ASC;

1. CROSS JOIN과 LEFT JOIN은 동시에 쓸 수 있다는 것을 몰랐다.

2. CROSS JOIN 후에 LEFT JOIN을 할 때 JOIN 조건으로 (ON 부분) student_id만 했다가 attended_exams가 틀렸는데 확인해 보니 subject_name도 JOIN 조건에 넣어야 한다는 것을 알고 고쳤다.