Getting a little stuck on this one:
Table defs below:
I have two tables, fedex_zones contains zip code to zone data, so for
example, zip 94947 is in zone 8
select zone from fedex_zones where zip = '94947'
> 8
Now, in the defex_rates table is how, based on weight, I can look up how
much it will cost to ship. Say the weight is 12.
select z_8 from fedex_rates where weight = 8
In part, my trouble is that I need to take the resuling zone from the first
select and use that to determine the field name.
I can easily do this in my code in 2 selects, but was hoping to be able to
get the price back in just one select, if possible.
mysql> describe fedex_rates;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| weight | int(11) | | | 0 | |
| z_2 | decimal(10,2) | | | 0.00 | |
| z_3 | decimal(10,2) | | | 0.00 | |
| z_4 | decimal(10,2) | | | 0.00 | |
| z_5 | decimal(10,2) | | | 0.00 | |
| z_6 | decimal(10,2) | | | 0.00 | |
| z_7 | decimal(10,2) | | | 0.00 | |
| z_8 | decimal(10,2) | | | 0.00 | |
| z_9 | decimal(10,2) | | | 0.00 | |
| z_10 | decimal(10,2) | | | 0.00 | |
| z_14 | decimal(10,2) | | | 0.00 | |
| z_17 | decimal(10,2) | | | 0.00 | |
| z_51 | decimal(10,2) | | | 0.00 | |
| z_54 | decimal(10,2) | | | 0.00 | |
| z_92 | decimal(10,2) | | | 0.00 | |
| z_96 | decimal(10,2) | | | 0.00 | |
| z_22 | decimal(10,2) | | | 0.00 | |
| z_23 | decimal(10,2) | | | 0.00 | |
| z_25 | decimal(10,2) | | | 0.00 | |
+--------+---------------+------+-----+---------+----------------+
mysql> describe fedex_zones;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| zip | char(5) | | UNI | | |
| zone | char(2) | | | | |
+-------+---------+------+-----+---------+----------------+
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
