homepage Welcome to WebmasterWorld Guest from 54.242.126.9
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Having Issues With First Dynamic Site
Trying to access infro two tables
u4eas

10+ Year Member



 
Msg#: 260 posted 12:18 pm on Jan 21, 2006 (gmt 0)

Ok, what I have is 1 DB that contains 2 tables

Table #1 = geocoding
Table #2 = stores

What I basically am trying to do is have a search box on a page that looks up a zipcode (zipid) from the geocoding table. It then finds the 4 closest stores (store1,store2,store3,store4 in the geocoding table)

Then I want to display the info from the stores table that corresponds to the store1,store2,store3,store4 fields from that specific zipid (Store name, address,city,state,zip,phone,fax,email,website,owners name)

Im really lost as to how to write that sql statement?

In the Geocode table I have these fields:
id, ZipdId, Store1, Store2, Store3, Store4 (and a couple more that we dont need to use)

In the stores table I have these fields:
storeNum (this is the number that would appear in the store1-4 fields in the geocode table), storename,address1, address2, city, state, zip, country, phone, fax, email, website, owner

Any help would be greatly appreciated. Im using an access db, with asp btw.

 

newbie2006

5+ Year Member



 
Msg#: 260 posted 11:43 pm on Jan 21, 2006 (gmt 0)

SELECT ..., stores.storename, stores.address1, ... FROM geocoding, stores WHERE geocoding.ZipId=#*$! AND (geocoding.Store1=stores.storeNum OR geocoding.Store2=stores.storeNum OR geocoding.Store3=stores.storeNum OR geocoding.Store4=stores.storeNum)

As for my mind, the newbie in SQl it has to be so.

aspdaddy

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 260 posted 12:28 pm on Jan 22, 2006 (gmt 0)

Your tables seem to be set up wrong, How does the zipID in geocoding relate to the zipCode in Stores?

If its the same thing then you could maybe use a query like this;

select * from stores
where storenum in (select store1,store2,store3,store4 from geocoding where zipId=@myZip)

IMO the way to do this feature professionally is to store the longtitue and lattitude values for your zip codes and write code to work out the distance between two zip codes.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved