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

Question

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?

Answer 1

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','[email protected]')
INSERT INTO #AllStudentsEmail VALUES (2000,'Jill B','[email protected]')
INSERT INTO #AllStudentsEmail VALUES (3000,'Tommy S','[email protected]')
INSERT INTO #AllStudentsEmail VALUES (4000,'Sally J','[email protected]')

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 [email protected]    NULL

Answer 2

You can use NOT IN in such situations, or LEFT JOIN, as follows (not sure what you mean by [CourseID] in your select list, though):

NOT IN

This may not be efficient for very large numbers of students.

Select 
 [StudentID], 
 [StudentName], 
 [StudentEmail],
 11 AS [CourseID] -- This is the missing course ID, specified as a constant
from [tbl_AllStudentsEmail]
where StudentID NOT IN (
  SELECT StudentID
  FROM tbl_CourseID
  where CourseID = 11)

LEFT JOIN

The filter on CourseID = 11 goes in the LEFT JOIN condition rather than than in WHERE.

Select 
 [StudentID], 
 [StudentName], 
 [StudentEmail],
 11 AS [CourseID] -- This is the missing course ID, specified as a constant
from [tbl_AllStudentsEmail]
left join tbl_CourseID
  on tbl_CourseID.StudentID = tbl_AllStudentsEmail.StudentID AND
     tbl_CourseID.CourseID = 11
where
 tbl_AllStudentsEmail.StudentID is not null