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

  • Thread starter Thread starter Richard Hollenbeck
  • Start date Start date
R

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]));
 
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.
 
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>

Richard Hollenbeck said:
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.

Richard Hollenbeck said:
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]));
Click to expand...
Click to expand...
 
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


Ken Snell said:
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>

Richard Hollenbeck said:
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.
Click to expand...
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]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top