Scott,
>...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...
If you have control over the data model, it would be good to change the
structure of fedex_rates to (id int PK, zone int, weight int, price
decimal(10,2)), getting rid of the denormalised z_* columns which are
causing you problems. Then a one-stage query would just be SELECT price
FROM fedex_rates WHERE zone=8 AND weight=12.
If you're stuck with the table structure you show, you're stuck with
two queries. If these lookup tables aren't large, there's probably not
much performance to be gained from hiding the two stages inside a
stored procedure, but if you want a one-step, IMO that's the way to go.
PB
-----
Scott Haneda wrote:
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) | | | | |
+-------+---------+------+-----+---------+----------------+
|
