cfquery that checks for values NOT existing in another cfquery

*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?

Judy DeoAsked:
Who is Participating?
 
danrosenthalCommented:
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
 
Judy DeoAuthor Commented:
oh forgot to mention, the dealer_number field in both tables is a TEXT field NOT a number field.
0
 
Judy DeoAuthor Commented:
correction:
oh forgot to mention, the dealernumber field in both tables is a TEXT field NOT a number field.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Tacobell777Commented:
SELECT DISTINCT dealernumber
 FROM tablename1
WHERE (delaernumber NOT IN(SELECT DISTINCT dealernumber  FROM tablename2))
0
 
anandkpCommented:
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
 
Judy DeoAuthor Commented:
oh wait, forgot to mention that tablename1 and tablename2 are in different databases, so different data source names.
how to change the query?
0
 
Judy DeoAuthor Commented:
or maybe there will have to more than one query since different datasources?
0
 
Judy DeoAuthor Commented:
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
 
danrosenthalCommented:
yes, quotedvaluelist would be required for non-numeric fields
0
 
Tacobell777Commented:
even though they are different databases you could have still done it with one query, just use

SELECT * FROM linkedservername.databasename.tablename.columnname
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.