Run a cfquery inside of a query output

Question

I am getting weird unexpected results when I try to run a cfquery inside of the output of a previous query.

Example:

<cfoutput query="reportInfo">
  <tr>
    <td>#id#</td>
    <td>#name#</td>    
    <cfif Status EQ 'Complete'>
      <cfquery name="submitInfo" datasource="RC">
        SELECT  *
        FROM    Action_Log
        WHERE Form_ID = '#id#'
        AND Action = 'Complete' OR Action = 'Saved'
      </cfquery>    
      <cfset startStamp = submitInfo.Action_Time>
      <cfoutput>startStamp</cfoutput>    
      <td>#startStamp#</td>
    <cfelse>
      <td>No Completion Date</td>
    </cfif>
  </tr>
</cfoutput>

When "StartStamp" is output to the page it is shown once for each count in the loop and it does this for each ID so there is a lot of extra.

I thought it should only run the submitInfo query once every time the contain query (reportInfo) output loops through but that is not happening.

Answer 1

(Expanded from comments ...)

<cfoutput>startStamp</cfoutput>

Not sure what you are trying to accomplish, but you should get rid of the inner cfoutput tags. Those are only needed when using the (frequently misunderstood) <cfoutput group="..."> feature. It is normally used to eliminate duplicates from sorted data. However, if the data is not sorted properly - or is grouped incorrectly - it can produce the kind of results you described. (If you are not familiar with grouping, see this example of creating an A-Z type listing).

Having said that, querying within a loop should be avoided. Most times you can achieve the same result with a simple JOIN. Without knowing more about the first query, I see no reason you cannot do that here.

In psuedo-sql something along these lines. It uses an OUTER JOIN to retrieve all records from the first table, but only the values from Action_Log that have a matching ID and status of "Complete" or "Saved".

SELECT  t1.ID
        , t1.Name
        , al.Action_Time AS CompletionDate
FROM    SomeTable t1
      LEFT JOIN Action_Log al 
                ON al.Form_ID = t1.ID 
                AND t1.Status = 'Complete'
                AND al.Action IN ('Complete','Saved')