?
Solved

cfquery that checks for values NOT existing in another cfquery

Posted on 2004-09-27
10
Medium Priority
?
243 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:Judy Deo
[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:Judy Deo
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:Judy Deo
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 600 total points
ID: 12165078
SELECT DISTINCT dealernumber
 FROM tablename1
WHERE (delaernumber NOT IN(SELECT DISTINCT dealernumber  FROM tablename2))
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 17

Assisted Solution

by:anandkp
anandkp earned 600 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:Judy Deo
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:Judy Deo
ID: 12172992
or maybe there will have to more than one query since different datasources?
0
 
LVL 15

Accepted Solution

by:
danrosenthal earned 800 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:Judy Deo
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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