trouble with nested query loops

[OccasionalCoder]

Hi:

Here's the code I'm using:
--------

<cfquery name="get_agency" datasource="#db#" username="#un#" password="#pw#">
select * from agency where agency_id = #url.agency_id#
</cfquery>


<cfoutput query="get_agency">
<h1>#agency_id# #agency_name#</h1>
<cfset thisagency = #get_agency.agency_id#>
</cfoutput>

<cfloop query="get_agency">
<cfquery name="get_division" datasource="#db#" username="#un#" password="#pw#">
select * from division where agency_id = #thisagency#
</cfquery>
<cfoutput query="get_division"><h2>#division_id# #division_name#</h2></cfoutput>
<cfset thisdivision=#get_division.division_id#>


<cfloop query="get_division">
<cfquery name="get_unit" datasource="#db#" username="#un#" password="#pw#">
select * from unit where division_id = #thisdivision#
</cfquery>
<cfoutput query="get_unit"><h3>#unit_id# #unit_name#</h3></cfoutput>

</cfloop>
</cfloop>
-----

Here's the results I'm getting:
------
2 Air Force Research Laboratory/Human Effectiveness Directorate (comes from agency table)
1 Biosciences & Protection Division (comes from division table)
2 Directed Energy Bioeffects Division (comes from division table)
3 Warfighter Interface Division (comes from division table)
4 Warfighter Training Research Division (comes from division table>
1 Biomechanics Division (HEPA) (comes from unit table)
2 Applied Biotechnology (HEPB) (comes from unit table)
1 Biomechanics Division (HEPA) (comes from unit table)
2 Applied Biotechnology (HEPB) (comes from unit table)
1 Biomechanics Division (HEPA) (comes from unit table)
2 Applied Biotechnology (HEPB) (comes from unit table)
1 Biomechanics Division (HEPA) (comes from unit table)
2 Applied Biotechnology (HEPB) (comes from unit table)
3 Human Effectiveness and Logistics (comes from unit table)
-------

Here's the results I want:

----

2 Air Force Research Laboratory/Human Effectiveness Directorate
1 Biosciences & Protection Division
1 Biomechanics Division (HEPA)
2 Applied Biotechnology (HEPB)
2 Directed Engery Bioeffects Division
3 Warfighter Interface Division
4 Warfighter Training Research Division
3 Human Effectiveness and Logistics

(I added indents to help clarify levels)

What am I doing wrong?

Thanks

 

[r937]

what are you doing wrong? not using a single join query instead of your nested queries

doing a query inside a loop is about as inefficient as it gets

take some timings on your existing page, and see if the execution doesn't improve dramatically once you replace everything with this --

<cfquery name="single_query" ...>
select a.agency_id
     , a.agency_name
     , d.division_id
     , d.division_name
     , u.unit_id
     , u.unit_name
  from agency as a
left outer
  join division as d
    on a.agency_id = d.agency_id
left outer
  join unit as u
    on u.division_id = d.division_id      
 where a.agency_id = #url.agency_id#
order
    by a.agency_name
     , d.division_name
     , u.unit_name 
</cfquery>

<cfoutput query="single_query" 
       group="agency_name">
  <h1>#agency_id# #agency_name#</h1>
  <cfoutput group="division_name">
    <h2>#division_id# #division_name#</h2>
    <cfoutput>
      <h3>#unit_id# #unit_name#</h3>
    </cfoutput>
  </cfoutput>
</cfoutput>

a classic example of the GROUP= parameter of CFOUTPUT


note the ORDER BY is crucial to the way the GROUP= parameters are nested



r937.com | rudy.ca

 

[OccasionalCoder]

Thanks again...I tried using joins earlier, but I believe my understanding of them added to the frustration. I've re-read info on them and think I'm clear on more of it. I agree it's a simpler way to do things.

However, I did get the following error when I tried to run the code and can't see the error.


Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'a.agency_id = d.agency_id left outer join unit as u on u.division_id = d.division_id'.

The error occurred in C:\Inetpub\

http://wwwroot\consortium\current_work_sites3.cfm:

line 87

85 : join unit as u
86 : on u.division_id = d.division_id
87 : where a.agency_id = #url.agency_id#
88 : order
89 : by a.agency_name


--------------------------------------------------------------------------------

SQL select a.agency_id , a.agency_name , d.division_id , d.division_name , u.unit_id , u.unit_name from agency as a left outer join division as d on a.agency_id = d.agency_id left outer join unit as u on u.division_id = d.division_id where a.agency_id = 2 order by a.agency_name , d.division_name , u.unit_name

 

[r937]

ah, microsoft access

you shoulda said that ;-)

  from [COLOR=red]([/color]
       agency as a
left outer
  join division as d
    on a.agency_id = d.agency_id
       [COLOR=red])[/color]
left outer
  join unit as u
    on u.division_id = d.division_id

r937.com | rudy.ca