Fundamentally you're problem is not having a way to represent "infinite" as the max value (TInt_EndSlab). The problem, which you clearly state, comes when you have slabs: 1-10, 11-100, and 101-200 and look for 500.
Under this scheme there is no record that matches, nor can there be one because 500 is not between 101 and 200. I suggest you allow TInt_EndSlab to be NULL or use 0.0 as a key signaling this is the highest slab. So the slab values in your example would look like: 1-10, 11-100, and 101-0.0 Now your single query would look like: /* $NumOfLicenses = 500 here */ SELECT TDbl_SlabRate FROM Slab_Master WHERE TInt_ProductID = 143 AND TInt_StartSlab <= $NumOfLicenses AND (( TInt_EndSlab >= $NumOfLicenses ) OR ( TInt_EndSlab = 0.0 )) Possible pitfalls?? - You must make sure there there is only one record per ProductID with 0.0 value (you must make sure that there is no overlap or missing values in this scheme anyway). As you add/modify the slab values you need to have code that will check all of them for that product. -I think (and I'm not an expert on MySQL) 0.0 will be faster to search on than NULL (you might try both and see) Good Luck, Frank On 5/29/02 4:47 AM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > From: "Srinivasan Ramakrishnan" <[EMAIL PROTECTED]> > Reply-To: "Srinivasan Ramakrishnan" <[EMAIL PROTECTED]> > Date: Wed, 29 May 2002 12:36:24 +0530 > To: <[EMAIL PROTECTED]> > Subject: Nested SQL query problem... > > 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 > -- Frank Flynn Poet, Artist & Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
