469,315 Members | 1,593 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

query to find the closest result:K@N@!:

------=_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--

Jul 19 '05 #1
0 1397

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.