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
