Comparing two queries yields one result too many.

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#">
</CFQUERY>

<CFIF checkJob.recordcount lte 0>
<cfset newRow = QueryAddRow(myQuery, 1)>
<cfset temp = QuerySetCell(myQuery, "var1", Query1.var1)>
<cfset temp = QuerySetCell(myQuery, "var2", Query1.var2)>
<cfset temp = QuerySetCell(myQuery, "var3", Query1.var3)>
<cfset temp = QuerySetCell(myQuery, "var4", Query1.var4)>
<cfset temp = QuerySetCell(myQuery, "var5", Query1.var5)>
</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

 

Hello Rizados,
You should avoid having the second query inside a loop - even running a single record search on the id or another indexed field 173 times in succession unecc. is just cruel.

Instead, if the tables are in the same schema, you should just run a query like

Select <whatever fields> FROM table1 WHERE <whatvever variable> NOT IN (select id from table2)

OR if they are not in the same schema, but are on the same server you can run schema.table1 and schema.table2 in a stored procedure or though mysql control panel.

Or if they are different servers - you can run generate the sql inside your loop
<CFLOOP query="query1">
<CFSET select_on = select_on & "and var1 <> #query1.var1#">
</CFLOOP>
<CFIF listlen(select_on," ") GT 1>
<CFSET selectect_on = listdeletat(select_on,1," ")> <!--remove first and--->
<CFELSE> <CFSET select_on = "var1 <> 0 "><!--- or some value it would never be---> </CFIF>
<CFQUERY NAME="checkJob" DBTYPE="query">
SELECT var1
FROM tableorqueryinquestion
WHERE #select_on#;
</CFQUERY>


something along those lines.

 

Unfortunately, each query's componentized, and query of queries doesn't deal well with NOT IN.

I'm using MSSQL.

That second option looks like one that could work, though.

The whether or it was correct or not became a moot point last night, discovered a flaw in the data. The loop was doing what it was supposed to do (if messy).

Thanks for the advice!