Hi, I'm working on a PHP based website that loads custom pricing for users where they logon.
I'm trying to write a query to find the custom price of an item. Here is the table with all the prices in it. mysql> DESCRIBE item_price; +--------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+-------+ | majver | int(10) unsigned | | PRI | 0 | | | minver | int(10) unsigned | | PRI | 0 | | | iid | int(10) unsigned | | PRI | 0 | | | price | float(6,2) | | | 0.00 | | +--------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) majver and minver identify which price schedule to use. Each customer has a different majver minver combination. iid is the key to the products table. price is the price of the item. The price schedule where majver=0 and minver=0 is called list price, and is the only schedule that has a price for every single item. All the other combinations of majver and minver make up an sub-list of items that sell at the same discount level. If there is no price for a majver, minver combo, an attempt is made to use the price where minver=0, otherwise list price is used. To get the price of one item, I would do the following. mysql> SELECT * FROM item_price WHERE iid=3 AND majver IN (0,1) AND minver IN (0,10) ORDER BY majver DESC, minver DESC; +--------+--------+-----+--------+ | majver | minver | iid | price | +--------+--------+-----+--------+ | 1 | 10 | 3 | 98.81 | <= first choice | 1 | 0 | 3 | 91.21 | <= second choice | 0 | 0 | 3 | 152.02 | <= last choice +--------+--------+-----+--------+ 3 rows in set (0.00 sec) Sorting the table puts the most relevent price first. I grab this row and ignore the rest. This type of query should never have more than 3 rows since the tree structure of item_price is only 3 levels deep. Most of the time I want to get information about more than one item at once. However this complicates things. I only want 1 row for each iid, specifically the row with the highest majver and minver for a given iid. EXAMPLES: mysql> SELECT * FROM item_price WHERE iid IN (3,4) AND majver IN (0,1) AND minver IN (0,10) ORDER BY majver DESC, minver DESC; +--------+--------+-----+--------+ | majver | minver | iid | price | +--------+--------+-----+--------+ | 1 | 10 | 3 | 98.81 | | 1 | 0 | 3 | 91.21 | <= Need to eliminate these rows | 0 | 0 | 3 | 152.02 | <= | 0 | 0 | 4 | 49.29 | +--------+--------+-----+--------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM item_price WHERE iid IN (3,5) AND majver IN (0,1) AND minver IN (0,10) ORDER BY majver DESC, minver DESC; +--------+--------+-----+--------+ | majver | minver | iid | price | +--------+--------+-----+--------+ | 1 | 10 | 3 | 98.81 | | 1 | 10 | 5 | 89.77 | | 1 | 0 | 3 | 91.21 | <= Need to eliminate these rows | 1 | 0 | 5 | 82.87 | <= | 0 | 0 | 3 | 152.02 | <= | 0 | 0 | 5 | 138.11 | <= +--------+--------+-----+--------+ 6 rows in set (0.00 sec) mysql> Is it possible to get a result set with 1 row for each iid specified, and the price where the majver and minver are the largest combination for each iid? I would like to be able to do this with one query. Currently I've had to look up each price individually, but a page that loads prices for 100+ items creates a lot of overhead in running separate queries. The webserver and mysql server are over 3000 miles apart, so bandwidth is kind of an issue, but processing power on the database server is not. Any help would be appreciated. Brian Newsham Krackeler Scientific Inc. [EMAIL PROTECTED] 518-462-4281 ext. 121 518-462-6011
