Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Loop within a loop

Posted on 2006-07-13
5
Medium Priority
?
247 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 2000 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

Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 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