Re: Query of Queries and GROUP - use CFLOOP?
I am using a cfloop condition approach to perform a standard branched tree operation. I have four tables and my query is pretty straight forward. The output is not working though. It is returning the correct amount of results and is organizing them properly but the values displayed are repeating. I've looked over it again and again. It returns the proper fields and order in MSSQL Server Management Studio Express... Any ideas? Here's my query SELECT FFState.StateID, FFState.StateName, FFStateRegion.RegionID, FFStateRegion.StateID AS Expr1, FFStateRegion.StateRegion, FFRegionCounty.CountyID, FFRegionCounty.RegionID AS Expr2, FFRegionCounty.StateID AS Expr3, FFRegionCounty.LocationName, FFPropertyLocation.PropertyLocationID, FFPropertyLocation.RegionCountyID, FFPropertyLocation.RegionStateRegionID, FFPropertyLocation.RegionStateID, FFPropertyLocation.PropertyLocationName FROM FFState INNER JOIN FFStateRegion ON FFState.StateID = FFStateRegion.StateID INNER JOIN FFRegionCounty ON FFStateRegion.RegionID = FFRegionCounty.RegionID INNER JOIN FFPropertyLocation ON FFRegionCounty.CountyID = FFPropertyLocation.RegionCountyID ORDER BY FFState.StateName ASC, FFStateRegion.RegionID ASC, FFRegionCounty.CountyID ASC, FFPropertyLocation.PropertyLocationName ASC ( I know the syntax as posted may not be preserved but it is in dreamweaver. ) Here's the output as I got it from another post. I did modify this a bit to fit the query and four loops instead of three loops. cfset i = 1 cfloop condition=i LTE #GetStates.recordcount# cfset StateGroup = GetStates.StateID[i] #GetStates.StateName# #GetStates.StateID# - cfloop condition=i LTE GetStates.recordcount AND StateGroup IS #GetStates.StateID[i]# cfset RegionGroup = GetStates.RegionID[i] #GetStates.StateRegion# #GetStates.RegionID#BR / cfloop condition=i LTE GetStates.recordcount AND RegionGroup IS #GetStates.RegionID[i]# cfset CountyGroup = GetStates.CountyID[i] #GetStates.LocationName# #GetStates.PropertyLocationID#BRimg src=images/1x1black.gif width=100% height=1 vspace=2 /BR / cfloop condition=i LTE GetStates.recordcount AND CountyGroup IS #GetStates.CountyID[i]# A HREF=property1.cfm?PropertyLocationID=#GetStates.PropertyLocationID# class=leftnav#GetStates.PropertyLocationName#/ABR /img src=images/1x1black.gif width=100% height=1 vspace=2 /BR / cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346469 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries and GROUP - use CFLOOP?
I am using a cfloop condition approach to perform a standard branched tree operation. I have four tables and my query is pretty straight forward. The output is not working though. It is returning the correct amount of results and is organizing them properly but the values displayed are repeating. I've looked over it again and again. It returns the proper fields and order in MSSQL Server Management Studio Express... Any ideas? Here's my query SELECT FFState.StateID, FFState.StateName, FFStateRegion.RegionID, FFStateRegion.StateID AS Expr1, FFStateRegion.StateRegion, FFRegionCounty.CountyID, FFRegionCounty.RegionID AS Expr2, FFRegionCounty.StateID AS Expr3, FFRegionCounty.LocationName, FFPropertyLocation.PropertyLocationID, FFPropertyLocation.RegionCountyID, FFPropertyLocation.RegionStateRegionID, FFPropertyLocation.RegionStateID, FFPropertyLocation.PropertyLocationName FROM FFState INNER JOIN FFStateRegion ON FFState.StateID = FFStateRegion.StateID INNER JOIN FFRegionCounty ON FFStateRegion.RegionID = FFRegionCounty.RegionID INNER JOIN FFPropertyLocation ON FFRegionCounty.CountyID = FFPropertyLocation.RegionCountyID ORDER BY FFState.StateName ASC, FFStateRegion.RegionID ASC, FFRegionCounty.CountyID ASC, FFPropertyLocation.PropertyLocationName ASC ( I know the syntax as posted may not be preserved but it is in dreamweaver. ) Here's the output as I got it from another post. I did modify this a bit to fit the query and four loops instead of three loops. cfset i = 1 cfloop condition=i LTE #GetStates.recordcount# cfset StateGroup = GetStates.StateID[i] #GetStates.StateName# #GetStates.StateID# - cfloop condition=i LTE GetStates.recordcount AND StateGroup IS #GetStates.StateID[i]# cfset RegionGroup = GetStates.RegionID[i] #GetStates.StateRegion# #GetStates.RegionID#BR / cfloop condition=i LTE GetStates.recordcount AND RegionGroup IS #GetStates.RegionID[i]# cfset CountyGroup = GetStates.CountyID[i] #GetStates.LocationName# #GetStates.PropertyLocationID#BRimg src=images/1x1black.gif width=100% height=1 vspace=2 /BR / cfloop condition=i LTE GetStates.recordcount AND CountyGroup IS #GetStates.CountyID[i]# A HREF=property1.cfm?PropertyLocationID=#GetStates.PropertyLocationID# class=leftnav#GetStates.PropertyLocationName#/ABR /img src=images/1x1black.gif width=100% height=1 vspace=2 /BR / cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346470 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries and GROUP - use CFLOOP?
use a cfoutput with group attribute instead? cfoutput query=GetStates group=StateID #GetStates.StateName# #GetStates.StateID# - cfoutput group=RegionID #GetStates.StateRegion# #GetStates.RegionID#BR / cfoutput group=CountryID cfoutput A HREF=property1.cfm?PropertyLocationID=#GetStates.PropertyLocationID# class=leftnav#GetStates.PropertyLocationName#/ABR /img src=images/1x1black.gif width=100% height=1 vspace=2 /BR / /cfoutput /cfoutput /cfoutput /cfoutput Azadi On 03/08/2011 09:59 , Chris Sizemore wrote: I am using a cfloop condition approach to perform a standard branched tree operation. I have four tables and my query is pretty straight forward. The output is not working though. It is returning the correct amount of results and is organizing them properly but the values displayed are repeating. I've looked over it again and again. It returns the proper fields and order in MSSQL Server Management Studio Express... Any ideas? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Query of Queries and GROUP - use CFLOOP?
Hi, Regarding the CFOUTPUT GROUP issue with query of queries, is it feasible to convert the output below to cfloop's? What I have is: (cfouptut group=region) Region 1 (cfoutput group=city) City1 (cfoutput) client1 client2 City2 client3 client4 Region 2 and so on. Since the GROUP parameter since to conflict with the query of queries (see previous message for details), can I achieve the same output with a CFLOOP tag? I tried a few combinations, but the output does not reflect the DB content (e.g., columns get grouped/merged incorrectly). Any pointers/ideas welcome. Regards, Roberto ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234183 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries and GROUP - use CFLOOP?
Roberto Perez wrote: Regarding the CFOUTPUT GROUP issue with query of queries, is it feasible to convert the output below to cfloop's? What I have is: (cfouptut group=region) Region 1 (cfoutput group=city) City1 (cfoutput) client1 client2 City2 client3 client4 Region 2 and so on. Something like: cfset i = 1 cfoutput cfloop condition=i LTE queryname.recordcount cfset groupval1 = queryname.groupcol1[i] #groupcol1[i]# cfloop condition=i LTE queryname.recordcount AND groupval1 IS groupcol1[i] cfset groupval2 = queryname.groupcol2[i] #groupcol2[i]# cfloop condition=i LTE queryname.recordcount AND groupval2 IS groupcol2[i] #ungroupedcol[i]# cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop cfset i = i + 1 /cfloop /cfoutput Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234186 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query of Queries and GROUP - use CFLOOP?
At 10:37 AM 3/4/2006, Jochem van Dieten wrote: Regarding the CFOUTPUT GROUP issue with query of queries, is it feasible to convert the output below to cfloop's? Something like: cfset i = 1 cfoutput cfloop condition=i LTE queryname.recordcount Thanks, I was trying loops with the query parameter instead. I'll try your code and report back. Thanks again, Roberto ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234188 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
