How to remove row from CFLoop query results

Question

I'm using loop through a query and joining a table when records exist to join.

Is there a way to then keep the row from returning altogether when there is no record to join on that row?

Edit: I missed an if statement in there. I'm looping through records and checking for a setup option, if that setup options exists in a record and there is no corresponding record in the query "someRecord" those are the records I do not want to return.

<cfloop query="myquery">
    <cfif listfindnocase(myquery.setup_option,"required_information")>
        <cfquery name="someRecord" dbtype="query">
            select * from second_table
            where key_id = '#myquery.key_id#'
        </cfquery>
        <cfif someRecord.recordcount eq 0>

        <!--- Need something here to remove this row from returning in the query --->

        </cfif>
    </cfif>
</cfloop>

Answer 1

OK, the model answer here is: do not do this in CFML, do it on the DB. CF is for string generation, not data manipulation.

The question is slightly misleading as it initially asks how to remove rows from the query, which - as it turns out - is not the requirement (see comments on the question). I have answered that question further down.

To simply exit an iteration of a loop, use the <cfcontinue>. This ends the current iteration of the loop immediately, and returns to the top of the code block and starts the next iteration. Using your own code example:

<cfloop query="myquery">
    <cfif listfindnocase(myquery.setup_option,"required_information")>
        <cfquery name="someRecord" dbtype="query">
            select * from second_table
            where key_id = '#myquery.key_id#'
        </cfquery>
        <cfif someRecord.recordcount eq 0>
            <cfcontinue>
        </cfif>
        <!--- handle the rows you *do* want to process here --->
    </cfif>
</cfloop>

However to answer the question of how to remove rows from a query, there's no elegant way of doing it. You have two inelegant options:

// pseudocode, for brevity
newQuery = queryNew(oldQuery.columnList)
loop (oldQuery)
    if the row is not wanted
        continue
    /if
    add a row to newQuery
    add the row data to newQuery
/loop

Or:

listOfRowsToExclude = someMechanismToArriveAtSaidList()
<cfquery name="newQuery" type="query">
    SELECT   *
    FROM     oldQuery
    WHERE    id NOT IN (listOfRowsToExclude)
    ORDER BY [same clause as for oldQuery]
</cfquery>

However by far the best advice to be had here is to do your data processing in the DB where it belongs. You should not be putting this sort of logic in a) your CFML app; b) in your view code, which is where I suspect all this is going.

Separate your logic from your display. And separate your data-processing logic from your application logic.