Solved

Loop within a loop

Posted on 2006-07-13
5
220 Views
Last Modified: 2013-12-24
I want to output a table where one of the columns pulls from a 2nd query with multiple returns.

My latest stab in the dark:

<cfoutput query="getList">
     <cfloop query="getOfficers">
     #getList.pdjobID[getList.currentRow]# - #getOfficers.officer#<br />
     </cfloop><br />
</cfoutput>

It loops the records from Query1, then outputs only the first record from Query2 for each Query1 record.

Returrns:

Query1.Record1 - Query2.Record1
Query1.Record2 - Query2.Record1
Query1.Record3 - Query2.Record1

What I want it to return:

Query1.Record1 - Query2.Record1, Query2.Record2, Query2.Record3
Query1.Record2 - Query2.Record1, Query2.Record2, Query2.Record3
Query1.Record3 - Query2.Record1

Thanks in advance.
0
Comment
Question by:kbtudor
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17103149
I've resolved a lot of nested looping issues by setting a variable before I open the loop.  Try this:

<cfoutput query="getList">
     <cfset myRow = getList.currentRow>
     <cfloop query="getOfficers">
     #getList.pdjobID[myRow]# - #getOfficers.officer#<br />
     </cfloop><br />
</cfoutput>
0
 

Author Comment

by:kbtudor
ID: 17103445
Same return.  It correctly outputs the IDs (Query1), but then outputs only Record1 from Query2 for each of the Q1 outputs.  Here are my queries (scaled down):

<cfquery name="getList" datasource="#datasource#">
SELECT TOP 5 pdjobID
FROM pd_offDuty_job
WHERE closed = '0'
</cfquery>

<cfquery name="getOfficers" datasource="#datasource#">
SELECT pdjobID, officer
FROM pd_offDuty_officer
WHERE pdjobID = '#getList.pdjobID#'
</cfquery>
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17103598
Try:

<cfloop query="getList">
  <cfset variables.jobID = getList.pdjobID>
  <cfloop query="getOfficers">
    <cfoutput>#variables.jobID# - #getOfficers.officer#</cfoutput><br>
  </cfloop>
</cfloop>
0
 
LVL 1

Accepted Solution

by:
sjaeger949 earned 500 total points
ID: 17103630
This is how I would code it
--------------------------------------------------------------------------------

<cfquery name="getList" datasource="#datasource#">
  SELECT TOP 5 pdjobID
  FROM pd_offDuty_job
  WHERE closed = '0'
</cfquery>

<cfloop query="getList">
  <cfquery name="getOfficers" datasource="#datasource#">
    SELECT pdjobID, officer
    FROM pd_offDuty_officer
    WHERE pdjobID = '#thisJobID#'
  </cfquery>

  <cfoutput>#thisJobID# - </cfoutput>

  <cfset count = 1>
  <cfloop query="getOfficers">
    <cfoutput>#getOfficers.officer#<cfif count LT getOfficers.recordcount>, </cfif></cfoutput>
    <cfset count = count + 1>
  </cfloop>
  <br />
</cfloop>
0
 

Author Comment

by:kbtudor
ID: 17103759
sjaeger949,

That works perfectly.  In earlier tries, I was putting the count around the whole thing and not just Q2.  

Thanks so much!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question