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

[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...

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

SKIP

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.lic60
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