All users from one tbl not in another tbl

[Guest]

I have 2 tables. One is a user table (tblUsers) and the 2nd is a table that
holds courses that users have taken (tblCourses). "ID" is the user id field
in tblUsers and "UserID" is the user id field in tblCourses.

I want to select all users from tblUsers who have not taken any courses (not
in tblCourses). I thought I would use a NOT IN subquery, but I cannot get it
right.

Also, there are 2 fields in tblUsers that are criteria: UserStatus = "EU"
and DisplayOnly <> "Y".

Can anyone help me figure this out?

Thanks in advance,
Clint

 

[Tom Ellison]

Dear Clint:

I believe the NOT IN query should be:

SELECT *
FROM tblUsers
WHERE ID NOT IN
(SELECT UserID
FROM tblCourses)

Does this work for you?

Another approach is:

SELECT U.*
FROM tblUsers U
LEFT JOIN tblCourses C
ON U.ID = C.UserID

Tom Ellison

 

[Guest]

I was able to get your 1st idea to work, but not the 2nd. However, the query
is way too slow. It takes minutes for it to run. My user table has just under
3K records and my course table has over 30K records in it. I'm not sure if
this makes a difference or not.

This is the SQL of the query in question:

SELECT tblUsers.EID
FROM tblUsers
WHERE (((tblUsers.EID) Not In (SELECT [UserEID] FROM
qryUniqueTransEndUsersWithoutTraining)));

qryUniqueTransEndUsersWithoutTraining filters my 30K + record table down to
just over 2K.

Is there a way to speed this up without me writing code?

Thanks!
Clint

 

[John Spencer]

Pardon me, but try Tom's second option. It is almost always faster.

SELECT tblUsers.EID
FROM tblUsers LEFT JOIN qryUniqueTransEndUsersWithoutTraining
On tblUsers.EID= qryUniqueTransEndUsersWithoutTraining.[UserEID]
WHERE qryUniqueTransEndUsersWithoutTraining.[UserEID] is Null

Also make sure that you have an index on the EID fields.

I was able to get your 1st idea to work, but not the 2nd. However, the
query
is way too slow. It takes minutes for it to run. My user table has just
under
3K records and my course table has over 30K records in it. I'm not sure if
this makes a difference or not.

This is the SQL of the query in question:

SELECT tblUsers.EID
FROM tblUsers
WHERE (((tblUsers.EID) Not In (SELECT [UserEID] FROM
qryUniqueTransEndUsersWithoutTraining)));

qryUniqueTransEndUsersWithoutTraining filters my 30K + record table down
to
just over 2K.

Is there a way to speed this up without me writing code?

Thanks!
Clint

Dear Clint:

I believe the NOT IN query should be:

SELECT *
FROM tblUsers
WHERE ID NOT IN
(SELECT UserID
FROM tblCourses)

Does this work for you?

Another approach is:

SELECT U.*
FROM tblUsers U
LEFT JOIN tblCourses C
ON U.ID = C.UserID

Tom Ellison