[PHP-DB] Re: Nested SQL query problem...

2002-05-30 Thread Hugh Bothwell 


"Srinivasan Ramakrishnan" <[EMAIL PROTECTED]> wrote in message
004701c206df$5a00f100$[EMAIL PROTECTED]">news:004701c206df$5a00f100$[EMAIL PROTECTED]...
> 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.

... looking at this, the 'high-limit' value seems redundant
(it's always next_highest_minimum - 1); I would suggest
getting rid of it, ie

Table slabPricing:
slabID INT AUTO_INCREMENT
productID INT
slabStart INT
slabRate FLOAT(10,2)


> For example, what is the cost of 500 licenses of Product_ID 143 ?

SELECT slabRate FROM slabPricing
WHERE productID=143 AND slabStart<=500
ORDER BY slabStart DESC
LIMIT 1

... this will retrieve the per-piece rate for the
largest applicable slab.



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: Nested SQL query problem...

2002-05-29 Thread Frank Flynn 

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

[PHP-DB] Re: Nested SQL query problem...

2002-05-29 Thread Zubarev Andrey 

Hello Srini,

Don't speak english! :-)

>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||
>++-+--+-+-++



>SELECT TDbl_SlabRateFROM Slab_Master WHERE TInt_ProductID = 143
> AND TInt_StartSlab = 101; /*$MAX = 101*/

My examples:

CREATE TABLE wm
(
   id int(3) unsigned default '0',
   tex char(24) default NULL,
   lic tinyint(3) unsigned default NULL
)

INSERT INTO wm VALUES("1",  "w",   "6");
INSERT INTO wm VALUES("2",  "zz",  "20");
INSERT INTO wm VALUES("1",  "www", "5");
INSERT INTO wm VALUES("1",  "ww",  "7");
INSERT INTO wm VALUES("5",  "z",   "2");
INSERT INTO wm VALUES("80", "zzz", "1");


My query:

SELECT a.id, a.tex, MAX(a.lic) AS licens
FROM wm a, wm b
WHERE a.id=1 AND a.id=b.id AND a.lic<60
GROUP BY a.id, a.tex
HAVING MAX(a.lic)=MAX(b.lic)
   ^
   
Result: MAX licens = 7

-- 
Best regards,
 Zubarev Andrey
 mailto:[EMAIL PROTECTED]
 miranda-icq: 115959232
 DelphiKingdom Team [http://www.delphikingdom.com]
 KZ - Ekibastuz


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php