Well since your queries don't match it's hard to come up with an exact answer but assuming the second_query.zip is the ZIP code for the first query..
select * from dbo.tblStores where phone <> '' AND lat IS NOT NULL AND long IS NOT NULL AND active = 1 AND zip IN ( select zip from zip where latitude between #lat1# AND #lat2# AND longitude between #lon1# AND #lon2# order by dist asc) or select * from dbo.tblStores stores where phone <> '' AND lat IS NOT NULL AND long IS NOT NULL AND active = 1 AND exists ( select 1 from zip zip where zip.zip = stores.zip latitude between #lat1# AND #lat2# AND longitude between #lon1# AND #lon2# order by dist asc ) On Tue, May 20, 2008 at 3:05 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Thank you. This is helpful. but I'm still stumped and it's the last thing I > have to fix for this Store locator with a Google Maps mashup. > > But If I'm feeding results from a zipcode query as a subquery into a store > query, how do I get the results from the store query to sort the store > results by the zipcode distance when the zipcode query is the subquery? Make > sense? > > Here's the two queries. > > SEARCH THE STORE DATABASE WITH THE ZIPCODE QUERY RESULTS > <cfquery name="get_stores" datasource="#datasource#"> > select * > from dbo.tblStores > where phone <> '' AND lat IS NOT NULL AND long IS NOT NULL AND active = 1 AND > zip IN ( > <cfqueryparam value="#zipquery.zipcodes#" cfsqltype="cf_sql_char" > list="yes" />) > </cfquery> > > > GET THE ZIPCODES BASED ON THE RADIUS AND ZIPCODE ENTERED BY USER > select zip, state, city, > SQRT( > SQUARE(#lat_miles# * (latitude - (#z1.latitude#))) > + > square(#lon_miles# * (longitude - (#z1.longitude#))) > ) as dist > from zip > where > latitude between #lat1# AND #lat2# > AND > longitude between #lon1# AND #lon2# > order by dist asc > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305768 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
