BKBK, I simplified the table in my example to make it easier, but below is the solution I came up with. My next step would be to try to do a cfstoredprocedure because of the time it is taking to loop 500 miles worth of zip codes, I'm shaky on the cfstoredprocedure side and the SQL server stored procedure side. Anyhow here is my current code that is working.and thanks again for all of your help. <CFHTTP url="http://www.zipcodeapi.com/rest/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/radius.json/34242/500/mile" method = "get" result="httpResp"> </cfhttp> <!---- <cfdump var="#httpResp#" /> <cfscript> writeDump(var = httpResp.filecontent, label = "Zipcodes"); </cfscript> ----> </cfoutput> <cfset zipCodeJSON='#httpresp.filecontent#'> <cfset zipCodeStructure=#deserializeJSON(zipCodeJSON)#> <!---- <cfdump var="#zipCodeStructure#"> ----> <cfscript> zipCodeQuery=queryNew("id,city,distance,state,zipCode","Integer,Varchar,Decimal,Varchar,Integer"); for (row=1; row lte arrayLen(zipCodeStructure.zip_codes); row=row+1) { rowdata=[row,zipCodeStructure.zip_codes[row].city,zipCodeStructure.zip_codes[row]. distance,zipCodeStructure.zip_codes[row].state,zipCodeStructure.zip_codes[row].zip_code]; queryAddRow(zipCodeQuery,rowdata); } zipCodeQuery.sort(zipCodeQuery.findColumn("distance"),true); </cfscript> <!---- writedump(zipcodequery.zipcode); writedump(zipCodeQuery); ----> <cfloop query=zipcodequery> <!---- add back in for make and order (if order other than distance is wanted, check what others do) b.makeid = '#makeid#' and c.makeid = '#makeid#' and *** see search.cfm for order *** add back in where c.vehicleid = d.vehicleid if I ONLY want live auctions -----> <cfquery datasource="#datasource#" dbname="#dbname#" password="#password#" name="getthem"> SELECT a.model, b.make, c.model_year,c.image1,c.vehicleid,c.trim_level,d.auctionid,d.enddater,d.openingprice,d.highbid,d.currentbid,d.origenddater,c.memberid,d.memberid,a.modelid, c.vin FROM modelsfinal a, makesfinal b,vehicles c,auctions d WHERE b.makeid = a.makeid and c.modelid = a.modelid and zip = #zipcode# group by vin, model, make, model_year, image1, trim_level, auctionid, enddater, openingprice,highbid,currentbid,origenddater,c.vehicleid,d.memberid,c.memberid,a.modelid </cfquery> <cfoutput query=getthem group=vin> <cfif #getthem.model_year # is ""> Model Blank - #zipcode# <cfelse> #zipcodequery.zipcode# #zipcodequery.city# #zipcodequery.distance# #getthem.make# #getthem.model#<br> </cfif> </cfoutput> </cfloop>
... View more