Query within a query...

[Guest_imported]

I have a results page which shows a list of all "applications" (and associated data) conforming to previous search criteria.
However, for each "application", I want to display all "application names" (1 application can have many application names) on the same line.

Eg.

Application Status Date Names
1000 New 18/10/00 Fred Smith, Joe Bloggs
1001 New 17/8/00 Tim Brown

I essentially need a CFOUTPUT within a CFOUTPUT, to construct all the names, but you can't do this in ColdFusion !

I'm using an Oracle database. Any help would be appreciated.

Simon.

 

[calbearroo]

Simon,

If you are using Oracle, are you using stored procedures?

Can you post your code so that the question is more clear? It looks like what you need could come from a single table on a database set up under Oracle.

Would it make any sense for you to do a loop within a loop to display the information you want?



[sig][/sig]

 

[Guest_imported]

I'm reading data straight from an Oracle table "Applications". We have another table "Applicant_Names", which holds all the names. There is a 1 to many relationship between Applications and Applicant_Names.

I have the initial cfquery to loop through Applications, but need another cfquery inside to go through each Applicant_Name for that Application.

 

[iza]

what you can do is to store the result of one of the queries in an array
then you only have to cfloop trough the array, and a cfquery can b inside a cfloop
i've done this before and it works
[sig][/sig]

 

[calbearroo]

That wss part of my question, Simon. If you already have two files (tables) that are linked via an internal index, all you should have to do is write on query for the whole answer. The tables are already JOINed.

Check with your Oracle guy. He can produce the results or tell you what to put in the SQL statement.

Paul [sig][/sig]

 

[tempoman]

The easiest way is to use a nested cfloop instead of the cfoutput.

e.g. <cfloop query=&quot;For application&quot;>
<cfset id=&quot;ApplicationID&quot;>
<cfquery .........>
&quot;Find all the people in that application&quot;
<cfloop query=&quot;All people for that application&quot;>
<cfoutput>
#id# #PeopleName#
</cfoutput>
</cfloop>
</cfloop>

I hope that helps

Thanx


[sig][/sig]

 

[SimonA]

Fantastic ! Thanks Tempoman and Calbearroo for your responses.

I've seen a lot of responses on this forum with people saying you can't do a cfoutput within cfouput. This is a great solution, and completely solved my problems !

Thanks again guys. [sig][/sig]