grumpasaurus grumpasaurus - 4 months ago 15
SQL Question

Right join a table to itself SQL

This is probably really basic but I'm stuck!

I have two tables, one that shows which classes students are in and another which shows what grades each student has received. I want to find out which students, from a specific class, do not have a grade.

I wrote this code as a start point, which finds a list of the pupils in a class that do have grades:

SELECT mem.student_id, mem.class_id
FROM class_memberships as mem
inner join grades as gr
on mem.student_id = gr.student_id
where mem.class_id = 12 and gr.grade_type = 18


That works a treat, but what I actually need is the students in the class who do not have grades, rather than those that do. I thought I could right join against the memberships table as follows, but it didn't work:

SELECT mem.student_id, mem.class_id
FROM class_memberships as mem
inner join grades as gr
on mem.student_id = gr.student_id
right join class_memberships as mem2
on mem.student_id = mem2.student_id
where mem.class_id = 12 and gr.grade_type = 18
and mem.student_id is null


The above does not return any rows and I know one student does not have a grade. I followed the examples on google for SQL right joins to arrive at the above. I guess this isn't working because of the 'mem.student_id is null' clause, but all the cheatsheets on google said I needed it.

Help please! I'm a newbie to SQL (and coding in general) so this is probably really basic but I couldn't find the answer anywhere. Thanks :)

Answer

You probably don't need a 3-way join at all:

SELECT student_id
FROM class_membership
LEFT JOIN grades ON (class_membership.student_id = grades.student_id
    AND class_membership.class_id = grades.class_id)
WHERE grades.student_id IS NULL

class_membership already has all the students listed, so you only need to figure out which ones DON'T have a grade, which means their "grade" entry for that class would be null.

Comments