on 5/10/05 8:29 PM, Peter Brawley at [EMAIL PROTECTED] 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.
