Solved

cfquery that checks for values NOT existing in another cfquery

Posted on 2004-09-27
10
233 Views
Last Modified: 2013-12-24
*Using MS Access Database*
- dealernumber in both tables is a field that contains a digit number, ex. 004, 128, 237, etc.

1st query gets all the distinct values that show up in the 'dealernumber' field in tablename1
2nd query gets all the distinct values that show up in the 'dealernumber' field in tablename2
 
Query #1:
****************
<cfquery name="GetDealersThatAreInTable" datasource="dsname1">
 SELECT DISTINCT dealernumber
 FROM tablename1
</cfquery>
*****************

Query #2:
****************
<cfquery name="GetDealerNumbers" datasource="dsname2">
 SELECT DISTINCT dealernumber
 FROM tablename2
</cfquery>
****************

How do i make a third query that finds those dealer_numbers that are in Query #2, but NOT in Query #1 ?
or is there a way to combine into just one query?

0
Comment
Question by:sagardeo
  • 5
  • 2
  • 2
  • +1
10 Comments
 

Author Comment

by:sagardeo
ID: 12164574
oh forgot to mention, the dealer_number field in both tables is a TEXT field NOT a number field.
0
 

Author Comment

by:sagardeo
ID: 12164581
correction:
oh forgot to mention, the dealernumber field in both tables is a TEXT field NOT a number field.
0
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 150 total points
ID: 12165078
SELECT DISTINCT dealernumber
 FROM tablename1
WHERE (delaernumber NOT IN(SELECT DISTINCT dealernumber  FROM tablename2))
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 17

Assisted Solution

by:anandkp
anandkp earned 150 total points
ID: 12166903
SELECT DISTINCT dealernumber
FROM tablename2
WHERE (delaernumber NOT EXISTS (SELECT DISTINCT dealernumber FROM tablename1 where tablename1.dealernumber = tablename2.dealernumber))

"Exists" are better as compared to "IN" ... as "IN" clause may cause issues with huge no. of records.

K'Rgds
Anand
0
 

Author Comment

by:sagardeo
ID: 12169884
oh wait, forgot to mention that tablename1 and tablename2 are in different databases, so different data source names.
how to change the query?
0
 

Author Comment

by:sagardeo
ID: 12172992
or maybe there will have to more than one query since different datasources?
0
 
LVL 15

Accepted Solution

by:
danrosenthal earned 200 total points
ID: 12173515
Query #1:
<cfquery name="GetDealersThatAreInTable" datasource="dsname1">
 SELECT DISTINCT dealernumber
 FROM tablename1
</cfquery>

Query #2:
<cfquery name="GetDealerNumbers" datasource="dsname2">
 SELECT DISTINCT dealernumber
 FROM tablename2 WHERE dealernumber NOT IN (#valuelist(GetDealersThatAreInTable.dealernumber)#)
</cfquery>
0
 

Author Comment

by:sagardeo
ID: 12174762
note for future readers of the post:

i used "QuotedValueList" function instead of "Valuelist" function since my "dealernumber" field was a text value. not sure if i had to do that, but it worked once i made that change. it was giving me a datatype mismatch error before i made that change.
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 12174782
yes, quotedvaluelist would be required for non-numeric fields
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12177422
even though they are different databases you could have still done it with one query, just use

SELECT * FROM linkedservername.databasename.tablename.columnname
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question