9 Replies Latest reply on Jul 17, 2009 6:39 AM by TLC-IT

    Looping over QoQ Where Clause

    fs22

      I have two queries. One is a main query that we pull from a form submission. The second is a small query resultset that lists different subgroups in my company and what main group they belong to. I pull several QoQ from the main query. In the main query results, there is a column that holds the subgroup information. I am trying to do a Q0Q where I group the data from the main query into the main groups for analysis. Here is an example...

       

      Main Query

      record 1 - subgroup 1

      record 2 - subgroup 2

      record 3 - subgroup 3

       

      Second Query

      Subgroup 1 - Main Group 1

      Subgroup 2 - Main Group 2

      Subgroup 3 - Main Group 1

       

      So I am trying to loop over a QoQ on the main query where the result set would contain the information only from Main Group 1. So record 2 would be eliminated. I have tried using an IN clause with a cfloop, but I run into syntax trouble with the comma. I also tried looping over the QoQ as a whole and the resulting dump is only the last record.

       

      If there is anything else you need, let me know.

       

      Any thoughts?

       

      Clay

       

      P.S. Here is a code sampling...

       

          <cfquery name="rsGroup" datasource="nps">
          SELECT *
          FROM "GROUP"
          WHERE GROUP.PrimaryGroup = '#form.primarygroup#'
          </cfquery>
          rsGroup - <cfdump var="#rsGroup#">

       

          <cfloop query="rsGroup" startrow="1" endrow="#rsGroup.RecordCount#">
              <cfquery name="rsGroupQoQ" dbtype="query">
              SELECT *
              FROM rsNPS
              WHERE rsNPS.grp = '#rsGroup.group#'
              </cfquery>
          </cfloop>
          rsGroupQoQ - <cfdump var="#rsGroupQoQ#"><cfabort>