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. -- ------------------------------------------------------------- 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/[EMAIL PROTECTED]
