SQL question: Show me students who have NOT taken a certain course?

Question

So I have these tables:

STUDENTS: Student ID - First name - Last name - Email

COURSES: Catalog ID - Course Name - Description

TERMS: Term ID - Start Date - End Date

COURSEINSTANCES: CourseInstance ID - Catalog ID - Term ID

STUDENTCOURSES: StudentCourse ID - CourseInstance ID - Student ID - Date added to database

This makes it easy to see which students have taken which courses. I'm not sure how to go about finding out which students have NOT taken a particular course.

Doing something like this:

WHERE ((CourseInstances.CatalogLookup)<>504)

will just give me a list of courses taken by students that do not equal catalog number 504 like this:

Tara - 501

Tara - 502

Tara - 505

John - 503

So for example I've taken 504. Therefore I do not want me to show up on this list. The SQL above will just show all of my courses that are not 504, but it will not exclude me from the list.

Any ideas? Is this possible?

Answer 1

You should read about outer joins.

Answer 2

homework? use set operators.

select all students MINUS select any student who has taken this course...

Answer 3

I prefer this syntax over outer joins, IMO it's easier to read:

select * 
from STUDENTS 
where StudentID not in 
(
    select StudentID 
    from STUDENTCOURSES s 
    inner join COURSEINSTANCES c on s.CourseInstanceID  = c.CourseInstanceID 
    where c.CatalogID = 504
)

In the nested query, you select the StudentIDs of all students who HAVE taken course 504.

Then, you select all the students whose StudentIDs are not included in the nested query.

EDIT:
As ChrisJ already said, the c and the s are aliases for the table names.
Without them, the query would look like this:

select * 
from STUDENTS 
where StudentID not in 
(
    select StudentID 
    from STUDENTCOURSES 
    inner join COURSEINSTANCES c on STUDENTCOURSES.CourseInstanceID = COURSEINSTANCES .CourseInstanceID 
    where CatalogID = 504
)

I always use aliases because:
a) I'm too lazy to type the table names more often than necessary
b) IMO it's easier to read, especially when you join tables with long names

Answer 4

Try something like this:

SELECT * 
FROM Users 
WHERE UserID NOT IN
( SELECT UserID
  FROM 
    Users
  INNER JOIN
    ClassesTaken ON Users.UserID = ClassesTaken.UserID AND ClassesTaken.ClassNumber = 504)

Answer 5

Three main ways in Access

• NOT IN (Be careful to exclude any NULLs if there is any possibility of them appearing in the sub query)
• OUTER JOIN and filter on NULL (may need DISTINCT added)
• NOT EXISTS

Other RDBMSs also have EXCEPT or MINUS

Answer 6

SELECT * FROM students 
WHERE studentId not in 
(SELECT distinct studentID FROM studentCourses WHERE courseInstanceID = 504)

Answer 7

select s.*
from Students s
where not exists(select 1 from 
                 StudentCourses sc 
                 join CourseInstances ci on sc.[CourseInstance ID] = ci.[CourseInstance ID]
                 where sc.[Student ID] = s.[Student ID] and ci.[Catalog ID] = 504)