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) |      |     |         |                |
+-------+---------+------+-----+---------+----------------+