Skip to main content
R
Rizados
Participating Frequently
August 22, 2007
Question

Comparing two queries yields one result too many

  • August 22, 2007
  • 3 replies
  • 639 views
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
    This topic has been closed for replies.

    3 replies

    A
    Amiable_Penguin15D5
    Participating Frequently
    August 22, 2007
    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=using_recordsets_7.html

    Maybe you can't use a SUB SELECT in a Q of Q.

    Try

    <cfquery name="myQuery" dbtype="query">
    SELECT var1, var2, var3, var4, var5
    from Query1
    WHERE var1 NOT IN (#valueList(Query2.var1)#)
    </cfquery>

    If var1 is a string, use QuotedValueList

    Edit: Using the correct JOIN in the original query would be the fastest way to get your data.
    C
    cf_dev2
    Inspiring
    August 22, 2007
    I don't know if QoQ's supports that type of IN clause. IIRC they only support an IN clause with constants.

    Have you tried a LEFT JOIN in a regular db query?

    SELECT t1.var1, t1.var2, t1.var3, t1.var4
    FROM yourTableA t1 LEFT JOIN yourTableB t2
    ON t1.var1 = t2.var1
    WHERE t2.var1 IS NULL
    R
    RizadosAuthor
    Participating Frequently
    August 22, 2007
    The problem is, the second query is used in far more places than just as a subset of the first. So we modularized both.
    A
    Amiable_Penguin15D5
    Participating Frequently
    August 22, 2007
    I don't usually run query or queries, but this should do the trick:

    SELECT var1
    FROM Query1
    Where var1 NOT IN (SELECT var1 FROM Query2)

    Of course, you should really do this as a single query to begin with.

    SELECT var1
    FROM Table1
    Where var1 NOT IN (SELECT var1 FROM Table2)
    R
    RizadosAuthor
    Participating Frequently
    August 22, 2007
    Whoops! It didn't like that.
    Code was:

    <cfquery name="myQuery" dbtype="query">
    SELECT var1, var2, var3, var4, var5
    from Query1
    WHERE var1 NOT IN (SELECT var1 from Query2)
    </cfquery>

    I get this error:

    Query Of Queries syntax error.
    Encountered "var1 NOT IN ( SELECT. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices