Solved

cfquery that checks for values NOT existing in another cfquery

Posted on 2004-09-27
10
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Schedule a Tour of the ATEN booth at InfoComm 2017

Tour the ATEN booth to see the the Latest Addition to the Modular Matrix Switch Series, New 4K HDMI Over IP Extender and more! Enter ATEN's Ultimate Giveaway Sweepstakes for a chance to win one of several great prizes, including an ATEN US7220 2-Port Thunderbolt 2 Sharing Switch!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
What You Need to Know when Searching for a Webhost Provider
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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