SELECT z.columnname, o.zipcode, o.name, o.blah...
FROM zipcodes z INNER JOIN OtherTableName O
WHERE blah...
More here:
http://www.devguru.com/Technologies/jetsql/quickref/inner_join.html
-Cameron
-----------------
Cameron Childress
Sumo Consulting Inc
---
land: 858.509.3098
cell: 678.637.5072
aim: cameroncf
email: [EMAIL PROTECTED]
-----Original Message-----
From: Harold Brauer [mailto:[EMAIL PROTECTED]
Sent: Friday, February 06, 2004 8:44 PM
To: CF-Talk
Subject: Zip Code Help
I have been working on this problem for days and I could really use a
fresh set of eyes on this problem. I have a zip code table which has all
of the information I need to calculate distances between zip codes and a
members table which has the person's zip code.
Here is the problem! I am getting a list of zip codes and distances
that meet the search criteria. From there I want to go through the list
and pull out the records from the members table where the zip code
matches one of the results from the previous query. I then want to
display the records. Easy enough, however I am running into the problem
of display. I am currently using the following code (note that I am
including the initial zip code search code as well):
<CFPARAM name="passedzipcode" type="string" default="78626">
<CFPARAM name="passedradius" type="string" default="20.0">
<CFPARAM Name="CurrentPage" Default="1">
<!--- Query To Get Zip Code From Zip Code Table --->
<cfquery name="passedzip" datasource="#application.dsn1#">
SELECT * FROM #tablename#
WHERE zipcode='#passedzipcode#'
</cfquery>
<cfif passedzip.recordcount eq 0>
<!--- Incorrect Zip Code --->
No Records
<cfabort>
</cfif>
<!--- Get Zip Codes That Match --->
<cfquery datasource="#application.dsn1#"
name="getlocs">
SELECT distinct zipcode, latitude, longitude,
ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) *
SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963, 0, 3) AS
distance
FROM zipcodes
Where latitude between (#passedzip.latitude# -
(#passedradius#/111)) and (#passedzip.latitude# + (#passedradius#/111))
and longitude between (#passedzip.longitude# -
(#passedradius#/111)) and (#passedzip.longitude# + (#passedradius#/111))
Order by distance
</cfquery>
<!--- Find The Records From The Zip Code List And Display The Distance
Variable --->
<cfoutput query="getlocs">
<cfquery name="zip_find" datasource="#application.dsn1#">
Select distinct member_id
From Contact_info
Where zip = '#getlocs.zipcode#'
</cfquery>
<cfif len(zip_find.member_id)>
#zip_find.Member_id# Zip Code: #getlocs.zipcode# Distance:
#getlocs.distance#<br>
</cfif>
</cfoutput>
Can anyone suggest a better way of doing this without using the
<Cfoutput> to loop through the query. I am using a custom tag call
CF_TrackMan through out my site so I can have a nice looking nav bar (1
2 3 >>) so the person can view the results. The way I have it set up now
this tag is not working. In addition, when I execute the second query
(getlocs) it is taking a long time to execute. At this time in my
development I do not have a lot of records in my members table, I am
afraid that once the table gets bigger and there are multiple
simultaneous requests on this script it will crash my server. Does
anyone know of a faster way to get this part done?
TIA
H
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
