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.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.