Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  SQLnewbie - Question on a Query

Author  Topic  ryanrat Starting Member 5 Posts Posted - 2001-11-01 : 16:25:38 Hello all! First, let me say thanks ahead of time. Next, my db layout. Here it is: SubInfo { *UserID UserName Password SubName Contact_First_Name Contact_Lasst_Name Email } Email { *Record UserID JobNumber EmailNote } JobInfo { *JobNumber JobKey JobName Owner Architect } Anything between #'s are ColdFusion Variables. *'s denote Primary Key. Okay - the following query retrieves all Sub's that are receiving email from a Jobnumber specified by #JobNumber# (again a variable that's passed into the query). 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#) Now, what I need is a query that retrieves all Users that ARE NOT receiving mail from the JobNumber specified by #JobNumber#. I thought that this would 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=JobInfo.JobNumber) AND (Email.JobNumber =#JobNumber#) AND (JobInfo.JobNumber = #JobNumber#) ...but when I use this query, I receive undesired results. If I have no records in Email, it returns nothing (should return all of the Subs). I then added records to the SubInfo table, populating them Sub1 - Sub5 (These are SubContractors) and have added two records to Email for a JobNumber for Sub1 and Sub2. My results for this query are as follows: 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 to the email table, and multiple copies for ones that have not. Any ideas?