MS sql query value one table but not another join table where*

join;

I have [tbl_AllStudentsEmail] where I get my students distribution list.
I also have [tbl_CourseID] where some students are registered (not all).
Students record are registered here along with the COURSEID.

Conditions: A student can be registered in several courses.

My goal is that I want to select all the students who are not registered to the course ID. How do I go about that?

Select 
 [StudentID], 
 [StudentName], 
 [StudentEmail],
 [CourseID]
from [tbl_AllStudentsEmail]
left join tbl_CourseID
on tbl_CourseID.StudentID = tbl_AllStudentsEmail.StudentID 
where
 CourseID = 11 and 
 tbl_AllStudentsEmail.StudentID is not null

This statement gives me a problem. It would give me "dupe" students email when the student is registered to course ID = 9 and CourseID = 10.

How can i get the UNIQUE student email out?

Best Answer

Assuming the following table structures and that you want to return students that have NOT registered for CourseID 11...you need to change your LEFT JOIN to include CourseID = 11 and then add CourseID is NULL in the WHERE clause to pull the students that are not registered for that course.

In your query having CourseID = 11 in your WHERE clause limits the results to the students that have registered for that course.

CREATE TABLE #AllStudentsEmail (StudentID INT, StudentName VARCHAR(20), StudentEmail VARCHAR(20))
CREATE TABLE #Course (CourseID INT, StudentID INT)

INSERT INTO #AllStudentsEmail VALUES (1000,'Joe T','jt@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (2000,'Jill B','jb@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (3000,'Tommy S','ts@blank.edu')
INSERT INTO #AllStudentsEmail VALUES (4000,'Sally J','sj@blank.edu')

INSERT INTO #Course VALUES (9,1000)
INSERT INTO #Course VALUES (10,1000)
INSERT INTO #Course VALUES (11,1000)
INSERT INTO #Course VALUES (9,2000)
INSERT INTO #Course VALUES (10,2000)
INSERT INTO #Course VALUES (11,2000)
INSERT INTO #Course VALUES (9,3000)
INSERT INTO #Course VALUES (10,3000)
INSERT INTO #Course VALUES (10,4000)
INSERT INTO #Course VALUES (11,4000)

SELECT  #AllStudentsEmail.[StudentID], 
 [StudentName], 
 [StudentEmail],
 [CourseID]
FROM #AllStudentsEmail
LEFT JOIN #Course ON #AllStudentsEmail.StudentID = #Course.StudentID 
    AND #Course.CourseID = 11
WHERE #Course.CourseID IS NULL

DROP TABLE #AllStudentsEmail
DROP TABLE #Course

--results 

StudentID   StudentName StudentEmail    CourseID
3000    Tommy S ts@blank.edu    NULL
Related Question