How to list only records that DO NOT match the criteria?

[Richard Hollenbeck]

If I list the students in the students table I get 204 students. But if I
add the studentsInCourses table to the query I get 129 students. This is
because when a student is dropped from a course they are not automatically
dropped from the students table.

I tried the following but it returned zero records:

SELECT students.studentID
FROM students INNER JOIN studentsInCourses ON students.studentID =
studentsInCourses.studentID
WHERE (((students.studentID)<>[studentsInCourses].[studentID]));

 

[Richard Hollenbeck]

Thanks, but never mind. I figured it out. Here's what I did:

SELECT *
FROM students
WHERE studentID NOT IN (Select studentID From studentsInCourses);

Now it returns exactly 75 students, the difference between 204 and 129.

 

[Ken Snell [MVP]]

That type of join can run fairly slow. Try this:

SELECT students.*
FROM students LEFT JOIN studentsInCourses
ON students.studentID = studentsInCourses.studentID
WHERE studentsInCourses.studentID Is Null;

--

Ken Snell
<MS ACCESS MVP>

Thanks, but never mind. I figured it out. Here's what I did:

SELECT *
FROM students
WHERE studentID NOT IN (Select studentID From studentsInCourses);

Now it returns exactly 75 students, the difference between 204 and 129.

If I list the students in the students table I get 204 students. But if I
add the studentsInCourses table to the query I get 129 students. This is
because when a student is dropped from a course they are not automatically
dropped from the students table.

I tried the following but it returned zero records:

SELECT students.studentID
FROM students INNER JOIN studentsInCourses ON students.studentID =
studentsInCourses.studentID
WHERE (((students.studentID)<>[studentsInCourses].[studentID]));

 

[Richard Hollenbeck]

WOW! Thanks. The query currently runs rather quickly, but perhaps it will
slow down as the database grows. I'll examine (and then try) this and get
back to the group. Thanks again.

Rich

That type of join can run fairly slow. Try this:

SELECT students.*
FROM students LEFT JOIN studentsInCourses
ON students.studentID = studentsInCourses.studentID
WHERE studentsInCourses.studentID Is Null;

--

Ken Snell
<MS ACCESS MVP>

Thanks, but never mind. I figured it out. Here's what I did:

SELECT *
FROM students
WHERE studentID NOT IN (Select studentID From studentsInCourses);

Now it returns exactly 75 students, the difference between 204 and 129.

if

I

add the studentsInCourses table to the query I get 129 students. This is
because when a student is dropped from a course they are not automatically
dropped from the students table.

I tried the following but it returned zero records:

SELECT students.studentID
FROM students INNER JOIN studentsInCourses ON students.studentID =
studentsInCourses.studentID
WHERE (((students.studentID)<>[studentsInCourses].[studentID]));