List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 11 2005 7:26am
Subject:Re: possible join
View as plain text  
Scott, sorry, my mistake,
  SELECT price
  FROM fedex_zones z
  INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
  WHERE r.weight = 25;

PB

----


Scott Haneda wrote:

>on 5/10/05 8:29 PM, Peter Brawley at peter.brawley@stripped wrote:
>
>  
>
>>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.
>>    
>>
>
>Ok, I changed the tables around a little, I can not really do this all in
>one table, since the data gets made new often by fedex, at any rate, (no pun
>intended :-))...
>
>mysql> describe fedex_zones;
>+-------+---------+------+-----+---------+----------------+
>| Field | Type    | Null | Key | Default | Extra          |
>+-------+---------+------+-----+---------+----------------+
>| id    | int(11) |      | PRI | NULL    | auto_increment |
>| zip   | char(5) |      | UNI |         |                |
>| zone  | char(2) |      |     |         |                |
>+-------+---------+------+-----+---------+----------------+
>
>mysql> describe fedex_rates;
>+--------+---------------+------+-----+---------+----------------+
>| Field  | Type          | Null | Key | Default | Extra          |
>+--------+---------------+------+-----+---------+----------------+
>| id     | int(11)       |      | PRI | NULL    | auto_increment |
>| weight | int(11)       |      |     | 0       |                |
>| zone   | int(11)       |      |     | 0       |                |
>| price  | decimal(10,2) |      |     | 0.00    |                |
>+--------+---------------+------+-----+---------+----------------+
>
>so first, I need to get the zone I am in, which is a:
>SELECT zone from fedex_zones where zip = 94947
>  
>
>>8
>>    
>>
>
>If the result in that case is 8, then I can
>select price from fedex_rates where zone = '8' and weight = '25'
>
>For some reason, this join is still not screaming out at me, or maybe I have
>it right, and my data is in duplication, any help is appreciated.
>  
>

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