Solved

Loop within a loop

Posted on 2006-07-13
5
212 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

Free camera licenses with purchase of My Cloud NAS

Milestone Arcus software is compatible with thousands of industry-leading cameras for added flexibility. Upon installation on your My Cloud NAS, you will receive two (2) camera licenses already enabled in the software. And for a limited time, get additional camera licenses FREE.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now