Help with SQL Query

[ryanrat]

I'm having a bit of trouble getting the results I need on a 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
}



Email tells me whether or not the UserID associated is to receive email.
If it's there, that UserID receives email, if not, he doesn't.

Below is the query I use to retrieve the SubInfo information where there is a record in Email:

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)
Notice that this query is specific to Email.Jobnumber
I use the output to populate a select box (labeled "Users currently receiving Email).

Now I need to populate the select box so that all the other users who are not receiving Email are output from the query, also specific to the Email.JobNumber.

I've tried some things but got some weird data back.
Can anyone help me out?

 

[tlbroadbent]

You do not have a relationship defined between JobInfo and the other tables. Even though you specify the JobNumber, I recommend that you define that relationship. It appears that the following should work.

Email.JobNumber = JobInfo.JobNumber Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ryanrat]

How exactly do you mean, define that relationship?

 

[tlbroadbent]

You defined a JOIN relationship between the Email and SubInfo tables. They are related by the UserID column.

Email.UserID = SubInfo.UserID

Your query lacks the JOIN relationship for the JobInfo table. If you use ANSI JOIN syntax it is much easier to see.

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

Using the JOIN style of your query, it would look 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=JobInfo.JobNumber)
AND (Email.JobNumber =#JobNumber#)
AND (JobInfo.JobNumber = #JobNumber#)

If one of these queries doesn't work, then post a little more detail about the problem. I don't know what you mean when you say you got weird data back. It would also help to know which RDMS you use because there are differences. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ryanrat]

I'm using Access via ODBC for all my queries.

This is my query that shows me who the userid's are that are receiving mail for the specific #jobnumber#:

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#)

I need a query that retrieves the userid's that are not receiving mail for the specific #jobnumber#. I thought originally, that this might do it:

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 the following happens: if there are no records, then nothing is returned, and if there is 1 record, then it returns the right data, but if there are multiple records, I get the same records returned multiple times, and one record is just returned once.

 

[ryanrat]

Okay - hope you're still looking at this thread. I had trouble with the ANSI Join syntax that you provided.

&quot;Syntax error (missing operator) in query expression 'SubInfo.UserID = Email.UserID INNER JOIN JobInfo ON Email.JobNumber = JobInfo.JobNumber'&quot;

The second query that you provided appears to retrieve the correct list of people who are receiving mail. Now I need another query that retrieves the list of people who are not receiving mail for that job. I would have thought that:

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

would have worked, but if there are no email records, then nothing is returned. I added some Email records to the table, UserID's 10 and 11. The following data was returned:

UserID SubName Email UserName
------ ------- ----- --------
10 Subcontractor1 sub1@sub1.com sub1
11 Subcontractor2 sub2@sub2.com sub2
12 Subcontractor3 sub3@sub3.com sub3
12 Subcontractor3 sub3@sub3.com sub3
13 Subcontractor4 sub4@sub4.com sub4
13 Subcontractor4 sub4@sub4.com sub4
14 Subcontractor5 sub5@sub5.com sub5
14 Subcontractor5 sub5@sub5.com sub5

As you can see I get 1 copy returned for the subs that have already been added the email db, and multiple copies for ones that have not.

Any Ideas?