Solved

Loop within a loop

Posted on 2006-07-13
5
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
What You Need to Know when Searching for a Webhost Provider
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

726 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