Re: Ranges query gone wild

(repost: first try bounced) "Boaz Amit" <boaz17@netvision.net.il> wrote in message news:000f01c3964d$b501fb40$6400a8c0@w5978.tdak.com... > > I have a MySQL table where a product price ranges are > set according to quantities. > For instance for a certain product you'd have (besides id columns): > > range_begin | range_end | range_price > 0 | 35 | 1.5 > 36 | 70 | 2 > 71 | - | 3 > > Where '-' is always the character that means '..and above' and closes the > entire range field. So in the above example a quantity of 71 and above has a > price tag - $3. First danger signal: duplication of data (or, storage of data that should be a result of calculation). For a given product, range_end = (range+1)_begin - 1 Second danger signal: needing a 'bad data' type. In some cases, may be necessary to have a separate valid-data field, but better to simply not allow bad data if at all possible. Instead, remove the redundant field: range_begin | range_price 0 | 1.5 36 | 2 71 | 3 and the query becomes SELECT range_price FROM price_ranges WHERE product_id = '$product_id' && size_id = '$size_id' && range_begin <= '$quantity' ORDER BY range_begin DESC LIMIT 1 -- Hugh Bothwell hugh_bothwell@hotmail.com Kingston ON Canada v3.1 GCS/E/AT d- s+: a- C+++ L++>+++$ P+ E- W+++$ N++ K? w++ M PS+ PE++ Y+ PGP+ t-- 5++ !X R+ tv b++++ DI+++ D-(++) G+ e(++) h-- r- y+