Problems retrieiving data with SQL Query

[ryanrat]

Below is a query that I am using with an Access DB over ODBC:

SELECT SubInfo.UserID, SubInfo.SubName, SubInfo.Email, SubInfo.UserName, JobInfo.JobName
FROM SubInfo, Email, JobInfo
WHERE (Email.UserID = SubInfo.UserID) AND (Email.JobNumber =#JobNumber#) AND (JobInfo.JobNumber = #JobNumber#)

(Anything with #'s are ColdFusion Variables)

What I need is the inverse of this query, and originally tried something like this:

SELECT SubInfo.UserID, SubInfo.SubName, SubInfo.Email, SubInfo.UserName, JobInfo.JobName
FROM SubInfo, Email, JobInfo
WHERE (Email.UserID <> SubInfo.UserID) AND (Email.JobNumber = #JobNumber#) AND (JobInfo.JobNumber = #JobNumber#)

But that produced some weird results. I tried doing a Minus, and found out that Access doesn't support the MINUS. So I used this:

SELECT SubInfo.UserID, Email.UserID, SubInfo.UserName, SubInfo.SubName, SubInfo.Email
FROM SubInfo LEFT JOIN Email ON SubInfo.UserID = Email.UserID
WHERE (((Email.UserID) Is Null));

But I need it to only return the records where JobInfo.JobNumber = #JobNumber#. Someone told me to use this:


SELECT SubInfo.UserID, SubInfo.UserName, SubInfo.SubName, SubInfo.Email
FROM SubInfo LEFT JOIN Email ON SubInfo.UserID = Email.UserID
WHERE (((Email.UserID) Is Null)) AND EXISTS
(SELECT *
FROM JobInfo
WHERE JobInfo.JobNumber = #JobNumber#);

But this seems to just give the same result as the previous query.

My tables are as follows:

SubInfo
{
UserID
UserName
Password
SubName
Contact_First_Name
Contact_Lasst_Name
Email
}

Email
{
Record
UserID
JobNumber
EmailNote
}

JobInfo
{
JobNumber
JobKey
JobName
Owner
Architect
}

Can anyone help me out?