Solved

cfquery that checks for values NOT existing in another cfquery

Posted on 2004-09-27
10
238 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reverse Proxy Server 6 101
Internal DNS Zone Issue 13 92
Websocket through haproxy reused connection causes "Not a valid frame" error. 4 147
Internal to DMZ IIS Authentication. 3 94
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…

732 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