1280. Students and Examinations : Leetcode

# student_id X subject_name
select st.student_id, st.student_name, su.subject_name
from Students st cross join Subjects su;

student_id	student_name	subject_name
1	Alice	Programming
1	Alice	Physics
1	Alice	Math
2	Bob	Programming
2	Bob	Physics
2	Bob	Math
13	John	Programming
13	John	Physics
13	John	Math
6	Alex	Programming
6	Alex	Physics
6	Alex	Math

# count attended_exams
select student_id, subject_name, count(subject_name)
from Examinations
group by student_id, subject_name;

student_id	subject_name	count(subject_name)
1	Math	3
1	Physics	2
1	Programming	1
2	Programming	1
13	Math	1
13	Programming	1
13	Physics	1
2	Math	1

answer

# combine above sqls
select st.student_id, st.student_name, su.subject_name, IFNULL(c.attended_exams, 0) as attended_exams
from Students st cross join Subjects su
    left join (select student_id, subject_name, count(subject_name) as attended_exams from 
    Examinations group by student_id, subject_name) as c
        on st.student_id = c.student_id AND su.subject_name = c.subject_name
order by student_id, subject_name;

student_id	student_name	subject_name	attended_exams
1	Alice	Math	3
1	Alice	Physics	2
1	Alice	Programming	1
2	Bob	Math	1
2	Bob	Physics	0
2	Bob	Programming	1
6	Alex	Math	0
6	Alex	Physics	0
6	Alex	Programming	0
13	John	Math	1
13	John	Physics	1
13	John	Programming	1