SQL Server : get all data from one table but condition on 2nd tables column

Question

I have 3 tables Student, Classes and StudentClasses.

Student table:

Classes table:

StudentClasses table:

What I am trying is

select 
    c.ClassID, sc.StudentID, Title, 
    ClassFrom as ClassDate, ClassTo as ClassTime,
    Duration, Type as SessionType, sc.Status as StatusJoin
from 
    Classes c 
left join 
    StudentClasses sc on sc.ClassID = c.ClassID 
where 
    sc.StudentID = '66919287-d63d-4b30-931f-30532b68c2f1' or 
    sc.StudentID IS NULL

This returns:

Which is correct I think, but when I change it to:

where sc.StudentID = '5a22f025-ae64-49b8-9782-32bc2f1ccef6'

this returns:

This should return all 4 classes with studentID and joining date null

Here is the question: I want here is all classes rather some student joins or not if joins then there should be Studentid and JoiningDate there other wise these columns can be null

Answer 1

use your condition in ON Clause instead of Where

 select c.ClassID,sc.StudentID,Title,ClassFrom as ClassDate,ClassTo as 
       ClassTime,Duration,Type as SessionType,sc.Status as StatusJoin
       from Classes c 
       left join StudentClasses sc on sc.ClassID = c.ClassID 
       and (sc.StudentID = '66919287-d63d-4b30-931f-30532b68c2f1' or 
       sc.StudentID IS NULL)