Solved

Loop within a loop

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

12 Experts available now in Live!

Get 1:1 Help Now