sql query not in join & between ranges date-CodePudding

I need some help with SQL Query. I am trying to find record who is in not join course in 2019 dates, i have 2 table

course_attendance
ID STUDENT_ID SCHEDULE_ID ATTEND_DATE

student ID NAME

i found who is not in course at all, but i need include range dates in 2019 who is not join course also,

here is the syntax

SELECT A2.* FROM student A2 
              WHERE A2.ID  NOT IN 
                       (Select student.ID FROM 
                                     student 
                                     inner join 
                                     course_attendance  
                                     on student.id = course_attendance.STUDENT_ID
                                     )

Result

3 Frank M 1994-01-04

4 James M 1996-06-23

5 Don M 1992-02-25

8 Benny M 1992-05-29

9 Pomela F 1993-02-09

11 Icarus M 1994-11-10

12 Angela F 1993-05-17

CodePudding user response：

You can use match(where) in your subquery

WHERE ATTEND_DATE=2019

CodePudding user response：

You can get your result by LEFT JOIN like this:

SELECT A2.* FROM student A2 
LEFT JOIN 
course_attendance CA on A2.Id=CA.STUDENT_ID AND DATEPART(YEAR,ATTEND_DATE)=2019
where CA.STUDENT_ID IS NULL