Forum Moderators: open
I've worked with databases for a while now, mostly doing the normal easy stuff like pulling something out that maches a variable value and the like.
Only now I want to tackle something else. I'd like to try to tackle data from two tables but can't seem to wrap my head around it. I've even got my trusty SQL for Dummies book, and boy do I feel like one!
What I am trying to do is figure out shipping cost based on zipcode and zone.
Here is an example of what the zip table looks like
(it shows only partial zipcodes, I can manage to get around that part myself, just need to figure out how to match it all)
(table) zip
zip fe
481 2
520 6
550 8
now for the shipping table
(table) shipping
weight 2 3 4 5 6 7 8
1 4.00 4.10 4.20 5.00 5.29 6.50 7.00
2 4.10 4.39 4.50 4.75 5.80 8.00 8.75
and so on. the numbers across the top of the prices correlates to the numbers in the zip table's field fe. I need to match the zipcode, grab the zone (number in fe of table zip) and then match the total weight of the order and pull out the price based on the correct zone number.
I think I stated that correctly so you could get what I am trying to do. I have looked at alot of other examples and tried to apply what I thought might work but to no avail.
Basically I think I want to say something like
select * from zip.zip where zip.zip LIKE zipcode, weight.shipping = weight, fe.zip.... and here's where I am stumped. I know that I need to be able to correlate the two. It would be easy if there were a matching id but alas fedex does not do this, the files are as you see them here.
Am I just spinning my wheels?
Thanks in advance for any help because it's most appreciated!
Bill
assuming, for sake of argument, zipcode=481 and totalweight=2, what if I do this:
select * from zip where zip = zipcode
and have that whole row in memory, then do a
select * from shipping where weight = totalweight
and have that whole row in memory (using different arrays so I can sift through them) and then perform some kind of a match where the value of fe (from the zip table), which is the correct zone, is matched up to the same column name in the shipping table? variable value equals column name. Hmmmm. Any thoughts?
