0

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?

0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.