[Top] [All Lists]

Re: Comparing two queries yields one result too many

Subject: Re: Comparing two queries yields one result too many
From: GArlington
Date: Thu, 23 Aug 2007 05:52:23 -0700
Newsgroups: macromedia.coldfusion.cfml_general_discussion
On 22 Aug, 23:15, "Rizados" <[email protected]> wrote:
> I have a problem that I'm pretty sure is resident in the structure of a loop,
> but I'm not quite sure how to fix it.
>  All of this is being done within a cfc. The cfc calls the first method for
> Query1, then calls the second method for Query2. Query1 has 173 records, 
> Query2
> has 117 records. Technically the difference should be 56 records.
>  However, the result of myquery (below) is giving me 57 records. And every one
> of them is a real record. Code as follows:
>  <CFSET myquery = QueryNew("var1, var2, var3, var4, var5")>
>  <CFLOOP INDEX="i" FROM="1" TO="#Query1.recordcount#">
>         <CFQUERY NAME="checkJob" DBTYPE="query">
>                                                 SELECT var1
>                                                 FROM Query2
>                                                 WHERE var1 = <cfqueryparam 
> cfsqltype="cf_sql_varchar"
> value="#Query1.var1[i]#">
>         </CFQUERY>
>         <CFIF checkJob.recordcount lte 0>
>                 <cfset newRow = QueryAddRow(myQuery, 1)>
>                 <cfset temp = QuerySetCell(myQuery, "var1", Query1.var1[i])>
>                 <cfset temp = QuerySetCell(myQuery, "var2", Query1.var2[i])>
>                 <cfset temp = QuerySetCell(myQuery, "var3", Query1.var3[i])>
>                 <cfset temp = QuerySetCell(myQuery, "var4", Query1.var4[i])>
>                 <cfset temp = QuerySetCell(myQuery, "var5", Query1.var5[i])>
>         </CFIF>
>  </CFLOOP>
>  <CFRETURN myQuery>
>  So if all is done correctly, I should be getting the results from Query1 that
> are NOT in Query2.
>  It's *almost* right.
>  Since var1 exists in all of the records returned by myquery, one presumes 
> that
> there's an extra record being returned that *IS* in Query2.
>  I'm not sure why, though.
>  The resultant screen needs to print out all of the variables from query1 that
> do not exist in query2. Hence, myquery.
>  Anyone have a better recommendation on how to fix this problem? I feel like a
> goober for even asking it, but it's been annoying me all afternoon.
>  Rizados:confused;

Assuming var1 has unique values in both queries your code should work,
but if query2 has duplicates...

<Prev in Thread] Current Thread [Next in Thread>