Solved

cfquery that checks for values NOT existing in another cfquery

Posted on 2004-09-27
10
214 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now