SQL Question 1

[ryanrat]

I'm using Access, and because access doesn't support the "Minus" operation, I need to run the following query:

<CFQUERY NAME=&quot;non_Email_Query_admin_emaillist&quot; DATASOURCE=#DataSource#
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));
</CFQUERY>

But I also need to add another Field for the query to retrieve(JobInfo.JobName = #form.JobName#). Can someone help me with the syntax?

Thanks,

Ryan

 

[Silvano]

try something like this:
<cfquery name=&quot;non_Email_Query_admin_emaillist&quot; datasource=&quot;#DataSource#&quot;>
SELECT SubInfo.UserID, Email.UserID, SubInfo.UserName, SubInfo.SubName, SubInfo.Email
FROM SubInfo LEFT JOIN Email
ON SubInfo.UserID = Email.UserID
WHERE Email.UserID = 'Null' AND EXISTS
(SELECT *
FROM JobInfo
WHERE JobName = '#form.JobName#');
</cfquery>

if the subquery is being used with the EXISTS operator, the subquery is evaluated for each row in the parent query; us the subquery has a result for that row, then the row is returned; the subquery SELECT clause must use the * wildcard Sylvano
dsylvano@hotmail.com

 

[ryanrat]

WWebSpider-
Thanks for your reply! When I run the query though, I get no results.

Also, I made a bit of a typo last time - I meant to say that the other field I need is (JobInfo.JobNumber = #form.JobNumber#), as opposed to JobName. Here is what I input into the editor.

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

Below are my tables:
SubInfo
{
UserID
UserName
Password
SubName
Contact_First_Name
Contact_Lasst_Name
Email
}

Email
{
Record
UserID
JobNumber
EmailNote
}

JobInfo
{
JobNumber
JobKey
JobName
Owner
Architect
}

 

[ryanrat]

WWebSpider-
I think I figured it out -

WHERE Email.UserID = 'NULL' AND EXISTS
(SELECT *
FROM JobInfo
WHERE JobNumber = '#JobNumber#');

On my Access DB, Email.UserID = 'NULL' should be Email.UserID IS NULL.

Thanks for your help. Now if I could get everything else to work... =]

 

[ryanrat]

It appears I have jumped the gun - I receive the same output as I did with my original query... Starting to pull my hair out. WWebSpider, you have any ideas as to what my problem is (besides being SQL stupid)?

 

[ryanrat]

Sorry to keep posting to my original message, but basically what I need is the OPPOSITE of the following:

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

 

[ryanrat]

Just to let you guys know - WWebSpider is awesome! Here's the SQL I ended using.

Select SubInfo.UserID, SubInfo.SubName,
SubInfo.Email, SubInfo.UserName
FROM SubInfo
WHERE SubInfo.UserID NOT IN(
SELECT SubInfo.UserID
FROM SubInfo
INNER JOIN Email on SubInfo.UserID = Email.UserID
WHERE Email.JobNumber = #JobNumber#)

Thanks to everyone!