Hi, I have a table that contains the prices of software products at different quantity slab rates.
I'm trying to get the Slab rate for a given number of licenses for a given product_id. For example, what is the cost of 500 licenses of Product_ID 143 ? The slabs do not continue for an infinite number, hence I may only have slab pricing for 1-10, 11-100, and 101-200 with the assumption that if there are no higher slabs and I wanted say the rate for 500 licenses, I would use the highest slab unit available, in this case the 101-200 slab. My table structure is like so: Slab_Master +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | TInt_SlabID | int(11) | | PRI | NULL | auto_increment | | TInt_ProductID | int(11) | YES | | NULL | | | TInt_StartSlab | int(11) | YES | | NULL | | | TInt_EndSlab | int(11) | YES | | NULL | | | TDbl_SlabRate | float(10,2) | YES | | NULL | | +----------------+-------------+------+-----+---------+----------------+ I achieve the result I want with the use of 2 queries: Query 1: SELECT MAX(TInt_StartSlab) FROM Slab_Master WHERE TInt_ProductID = 143 AND TInt_StartSlab < 500; Which gives: +---------------------+ | MAX(TInt_StartSlab) | +---------------------+ | 101 | +---------------------+ This I store in a variable, say $MAX, and execute Query 2; Query 2: SELECT TDbl_SlabRate FROM Slab_Master WHERE TInt_ProductID = 143 AND TInt_StartSlab = 101; /*$MAX = 101*/ Which gives: +---------------+ | TDbl_SlabRate | +---------------+ | 80.00 | +---------------+ I would ideally like to combine this into a single nested SQL query, however all my attempts at that have failed, and I remember reading somewhere that MySQL does not support nested SQL queries. Is a single query possible? Cheers, -Srini -- http://symonds.net/~sriniram -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
