How to combine query results?

Question

I have three queries that are tied together. The final output requires multiple loops over the queries. This way works just fine but seems very inefficient and too complex in my opinion. Here is what I have:

Query 1:

<cfquery name="qryTypes" datasource="#application.datasource#">
   SELECT 
      t.type_id, 
      t.category_id,
      c.category_name, 
      s.type_shortcode
   FROM type t
      INNER JOIN section s 
         ON s.type_id = t.type_id
      INNER JOIN category c 
         ON c.category_id = t.category_id
   WHERE t.rec_id = 45 -- This parameter is passed from form field.
   ORDER BY s.type_name,c.category_name
</cfquery>

Query Types will produce this set of results:

4   11  SP  PRES
4   12  CH  PRES
4   13  MS  PRES
4   14  XN  PRES

Then loop over query Types and get the records from another query for each record that match:

Query 2:

<cfloop query="qryTypes">
   <cfquery name="qryLocation" datasource=#application.datasource#>
      SELECT l.location_id, l.spent_amount
      FROM locations l
      WHERE l.location_type = '#trim(category_name)#' 
         AND l.nofa_id = 45 -- This is form field    
         AND l.location_id = '#trim(category_id)##trim(type_id)#'
      GROUP BY l.location_id,l.spent_amount
      ORDER BY l.location_id ASC
   </cfquery>

   <cfset spent_total = arraySum(qryLocation['spent_amount']) />
   <cfset amount_total = 0 />

   <cfloop query="qryLocation">           
      <cfquery name="qryFunds" datasource=#application.datasource#>
         SELECT sum(budget) AS budget
         FROM funds f
         WHERE f.location_id= '#qryLocation.location_id#' 
            AND nofa_id = 45
      </cfquery>

      <cfscript>
         if(qryFunds.budgetgt 0) {
            amount_total = amount_total + qryFunds.budget;
         }
      </cfscript>
   </cfloop>

   <cfset GrandTotal = GrandTotal + spent_total />
   <cfset GrandTotalad = GrandTotalad + amount_total />
</cfloop>

After the loops are completed this is result:

CATEGORY NAME   SPENT TOTAL   AMOUNT TOTAL
      SP           970927         89613
      CH           4804           8759
      MS           9922           21436
      XN           39398          4602
   Grand Total:    1025051        124410

Is there a good way to merge this together and have only one query instead of three queries and inner loops? I was wondering if this might be a good fit for a stored procedure and then do all data manipulations in there? If anyone have suggestions please let me know.

Answer 1

• qryTypes returns X records
• qryLocation returns Y records

So far you've run (1 + X) queries.

• qryFunds returns Z records

Now you've run (1 + X)(Y) queries.

The more data each returns, the more queries you'll run. Obviously not good.

If all you want is the final totals for each category, in a stored procedure, you could create a temp table with the joined data from qryTypes and qryLocation. Then your last qryFunds is just joined against that temp table data.

SELECT 
    sum(budget) AS budget
FROM 
    funds f
INNER JOIN 
    #TEMP_TABLE t ON t.location_id = f.location_id
AND 
    nofa_id = 45

You could then get other sums off the temp table if needed. It's possible this could all be worked into a single query, but maybe this helps you get there.

Also, a stored procedure can return multiple record sets, so you can have one return the aggregated table amount data and a 2nd return the grand total. This would keep all the calculations on the database and no need for CF to be involved.