------=_NextPart_000_004F_01C352B1.E5B8FA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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=3D0 and minver=3D0 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=3D0, =
otherwise list price is used.
To get the price of one item, I would do the following.
mysql> SELECT * FROM item_price WHERE iid=3D3 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 | <=3D first choice
| 1 | 0 | 3 | 91.21 | <=3D second choice
| 0 | 0 | 3 | 152.02 | <=3D 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 | <=3D Need to eliminate these rows
| 0 | 0 | 3 | 152.02 | <=3D
| 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 | <=3D Need to eliminate these rows
| 1 | 0 | 5 | 82.87 | <=3D
| 0 | 0 | 3 | 152.02 | <=3D
| 0 | 0 | 5 | 138.11 | <=3D
+--------+--------+-----+--------+
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.
be*@krackeler.com
518-462-4281 ext. 121
518-462-6011
------=_NextPart_000_004F_01C352B1.E5B8FA20--
