How to fetch all recordsets which are linked through that table?

I have three tables, with these fields:

classes: class_id | name | grade

classes_students: class_id | student_id

students: student_id | name

Classes has a 1:n relationship with Students, so one class can have many students. I want to select all students of a particular class, where class_id is 5.

Could I just do something like this?

SELECT student.name FROM students student 
LEFT JOIN classes_students link 
    ON link.class_id = 5 
    AND link.student_id = student.student_id

I’m not sure about if I need the join here and if that has to be a “LEFT JOIN”? What I want is only a record set containing the student names.

Answer

Use:

SELECT s.name
  FROM STUDENTS s
  JOIN CLASSES_STUDENTS cs ON cs.student_id = s.student_id
                          AND cs.class_id = 5

Alternately:

SELECT s.name
  FROM STUDENTS s
  JOIN CLASSES_STUDENTS cs ON cs.student_id = s.student_id
  JOIN CLASSES c ON c.class_id = cs.class_id
 WHERE c.class_id = 5

Because you want students only in a particular class, you’d use an INNER JOIN. You’d use a LEFT JOIN if you wanted say a list of all the students, and then LEFT JOIN to CLASSES_STUDENTS to know if any are taking classes (because those that aren’t would have null values in the CLASSES_STUDENTS columns). I recommend looking at this link for the breakdown of various JOINs and their impact on the data you’ll get back.