And also use INNER JOIN syntax.
-----Original Message----- From: Ray Champagne [mailto:[EMAIL PROTECTED] Sent: 24 June 2005 06:22 To: CF-Newbie Subject: Re: Nested loop problem It's too late for me to test this out, but it looks like you'd really want to collapse all three of those queries into one query that grabs everything. Something like: SELECT * FROM Agents A, Properties P WHERE A.AgentID = P.AgentID (Of course, you shouldn't use *, you should specify which fields you want.) There are way too many loops in there, you could grab everything you need in one query, then just loop over that and output everything that way. It will cut down quite a bit on processing time, too. This should get you started. I'm sure you can get some more concrete answers tomorrow when this list is more active. I'm off to go home...and sleep....finally... Ray Tasha Williams wrote: > I am brand new to cold fusion and am trying to make a page that lists statistics for 12 realtors in tabular format, but for some reason the statistics tables are listed 12 times instead of only once. I think that there is either something wrong with the cfloop or cfoutput, but I have not been able to figure it out with my limited knowledge. I've asked for help from some in my class and my professor, but all are stumped. Can anyone help? The source is listed below and the page is located at http://students.libsci.sc.edu/williamst/WilliamsRealtors/agentStatistics.cfm > Thanks, > T Wms. > <cfquery datasource="williamst" name="getAgents"> > SELECT DISTINCT AgentID FROM Properties > </cfquery> > <cfloop query="getAgents"> > <cfquery datasource="williamst" name="getNames"> > SELECT AgentID, FirstName, MiddleInitial, LastName from Agents > </cfquery> > > <html> > <head> > <title>Price Statistics</title> > <style> > TD { > font-family : "Times New Roman"; color : "white"; > } > H3 { > font-family : "Times New Roman"; color : "white"; > } > </style> > </head> > <body bgcolor="#000066"> > <hr align="center" color="white" width="300"> > <cfloop query="getNames"> > <h3 align="center">Price Statistics for Agent - <CFOUTPUT>#FirstName#</CFOUTPUT> <CFOUTPUT>#MiddleInitial#</CFOUTPUT> <CFOUTPUT>#LastName#</CFOUTPUT></h3> > <hr align="center" color="white" width="300"> > <cfquery datasource="williamst" name="getStats"> > SELECT count(Price) as Num, sum(Price) as Total, avg(Price) as Avg, max(Price) as Max, min(Price) as Min from Properties > WHERE AgentID = '#AgentID#' > </cfquery> > <cfloop query="getStats"> > <cfoutput> > <table align="center" border="1" bordercolor="white" cellspacing="0"> > <tr align="right"> > <td>Number of properties:</td> > <td>#Num#</td> > </tr> > <tr align="right"> > <td>Total price:</td> > <td>#Dollarformat(Total)#</td> > </tr> > <tr align="right"> > <td>Average price:</td> > <td>#Dollarformat(Avg)#</td> > </tr> > <tr align="right"> > <td>Maximum price:</td> > <td>#Dollarformat(Max)#</td> > </tr> > <tr align="right"> > <td>Minimum price:</td> > <td>#Dollarformat(Min)#</td> > </tr> > </table> > </cfoutput> > <hr align="center" color="white" width="300"> > > </b></td> > </cfloop> > </cfloop> > </cfloop> > </tr> > </table> > </body> > </html> > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1197 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
