You're right this can be done with a simple query
<cfquery name="myQuery" datasource="foo">
SELECT field_a
FROM table
WHERE field_a NOT IN (SELECT field_b FROM table)
</cfquery>
<cfoutput query="myQuery">
This is bad = #field_a#
> -----Original Message-----
> From: Bud [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 14, 2000 3:57 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Comparing values in 2 queries
>
>
> On 9/14/00, Roger Lim penned:
> >Hi,
> >
> >Wanna ask for help here. Let's say that I have 2 queries which have a
> >similar field column name. I wish to do a comparision and it's a AND
> >critieria.
> >
> >For example,
> >
> >Field_a Field_b
> >1 1
> >2 2
> >3 4
> > 5
> >
> >I wish to compare field_a with field_b and everything that's
> in field_a has
> >to be found in field_b, else it'll fail. How do I go about
> doing that ?
>
> I'm absolutely certain that someone else will come up with a simpler
> solution, but here's mine. :)
>
> <CFPARAM NAME="row" DEFAULT="0">
> <cfloop query="query1"><cfset row = row + 1>
> <CFOUTPUT query="query2" startrow="#row#" maxrows="1">
> <CFIF query1.field_a NEQ query2.field_b><cfset badrow =
> row><cfbreak></CFIF>
> </CFOUTPUT>
> </cfloop>
>
> <CFIF isDefined('badrow')>
> There were non matching values in row <CFOUTPUT>#badrow#</CFOUTPUT>!
> <cfelse>
> All records match!
> </CFIF>
>
> The neat thing is, you could also pass a form to continue testing and
> come up with the next bad row.
>
> <CFIF isDefined('badrow')>
>
> There were non matching values in row
> <CFOUTPUT>#badrow#</CFOUTPUT>!<p>
> <form action="thispage.cfm" method="post">
> <input type="Submit" value="Continue Testing">
> <CFOUTPUT>
> <input type="hidden" name="row" value="#badrow#">
> </CFOUTPUT>
> </form>
>
> <cfelse>
> All records match!
> </CFIF>
>
> Of course, rather than using cfbreak, you could set an empty list
> before the loop.
> <cfset badrows = "">
> The in lieu of cfbreak:
> <cfset badrows = listappend(badrows, badrow)>
>
> Then after the loop.
>
> <cfif badrows is not "">
> These rows did not match!<p>
> <CFOUTPUT>
> <cfloop index="thisrow" list='#badrows#'>
> #thisrow#<br>
> </cfloop>
> </CFOUTPUT>
> <cfelse>
> All records match!
> </CFIF>
> --
>
> Bud Schneehagen - Tropical Web Creations
>
> _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> ColdFusion Solutions / eCommerce Development
> [EMAIL PROTECTED]
> http://www.twcreations.com/
> 954.721.3452
> --------------------------------------------------------------
> ----------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=list
s/cf_talk or send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
