List:General Discussion« Previous MessageNext Message »
From:mfatene Date:May 11 2005 6:14am
Subject:Re: possible join
View as plain text  
Hi,
>>> SELECT zone from fedex_zones where zip = 94947
>>> select price from fedex_rates where zone = '8' and weight = '25'

your query should be :

select price from fedex_rates, fedex_zones
where fedex_zones.zip = 94947
      and fedex_zones.zip = fedex_rates.zip
      and fedex_rates.weight = '25'


Mathias



Selon Scott Haneda <lists@stripped>:

> 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.
> --
> -------------------------------------------------------------
> Scott Haneda                                Tel: 415.898.2602
> <http://www.newgeo.com>                     Novato, CA U.S.A.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


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