List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 11 2005 3:29am
Subject:Re: possible join
View as plain text  
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) |      |     |         |                |
>+-------+---------+------+-----+---------+----------------+
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005
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