List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:May 11 2005 2:50am
Subject:possible join
View as plain text  
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.


Thread
possible joinScott Haneda11 May
  • Re: possible joinPeter Brawley11 May
    • Re: possible joinScott Haneda11 May
      • Re: possible joinmfatene11 May
      • Re: possible joinPeter Brawley11 May
      • just hiUnknown Sender11 May
        • Re: just hiJohan Höök11 May
      • Re: possible joinHarald Fuchs11 May
        • Re: possible joinSGreen11 May
        • Re: possible joinPeter Brawley11 May
Re: just hiNestor Florez11 May