advice on query

Hi,
my question is - how do I get just the best price from this query:
item is a shopping list
prod is the product list
price is the list of prices, some product have multiple prices for different
quantities
eg Prod price minquant
6 3.53 1
6 3.22 30
6 2.95 100
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
gives
20 1 1 Kistenspanners K-10 4.5
18 5 1 De-Sta-Co #311 22.42
21 6 35 Spring 3.53
21 6 35 Spring 3.22
21 6 35 Spring 2.95
I just want the price relevant to the quantity - eg in eaxample above, 35
Springs qualifies for >30 price of 3.22.
Appreciate any help!
thansk,
NEILwell, i didn't have time to recreate the tables and data you listed
without the DDL, but i think you need a subquery. try adding something
like this to your WHERE clause, and you should get only the most
appropriate quantity:
AND (item.iQuant > (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))
for a whole query of:
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
AND (item.iQuant > (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))
hope this helps
jason|||actually, it should probably look more like this:
AND (price.minquant = (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))|||You can try this:
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
and minquant = (select max(minquant)
from mms_tblProductPriceList price2
where price2.minquant < item.iQuant
prod.ProductID = price2.ProductID)
Perayu
"Neil Jarman" wrote:

> Hi,
> my question is - how do I get just the best price from this query:
> item is a shopping list
> prod is the product list
> price is the list of prices, some product have multiple prices for differe
nt
> quantities
> eg Prod price minquant
> 6 3.53 1
> 6 3.22 30
> 6 2.95 100
> SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
> prod.sShortDesc, price.cPrice
> FROM mms_tblOrderItemList item INNER JOIN
> mms_tblProductList prod ON item.ProductID =
> prod.ProductID INNER JOIN
> mms_tblProductPriceList price ON prod.ProductID =
> price.ProductID
> WHERE (item.OrderHeadID = 9)
> gives
> 20 1 1 Kistenspanners K-10 4.5
> 18 5 1 De-Sta-Co #311 22.42
> 21 6 35 Spring 3.53
> 21 6 35 Spring 3.22
> 21 6 35 Spring 2.95
> I just want the price relevant to the quantity - eg in eaxample above, 35
> Springs qualifies for >30 price of 3.22.
> Appreciate any help!
> thansk,
> NEIL
>
>|||Thank you both sooooo much - I was really stuck with that one!!!
NEIL
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:dekp74$kvk$1$8300dec7@.news.demon.co.uk...
> Hi,
> my question is - how do I get just the best price from this query:
> item is a shopping list
> prod is the product list
> price is the list of prices, some product have multiple prices for
> different quantities
> eg Prod price minquant
> 6 3.53 1
> 6 3.22 30
> 6 2.95 100
> SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
> prod.sShortDesc, price.cPrice
> FROM mms_tblOrderItemList item INNER JOIN
> mms_tblProductList prod ON item.ProductID =
> prod.ProductID INNER JOIN
> mms_tblProductPriceList price ON prod.ProductID =
> price.ProductID
> WHERE (item.OrderHeadID = 9)
> gives
> 20 1 1 Kistenspanners K-10 4.5
> 18 5 1 De-Sta-Co #311 22.42
> 21 6 35 Spring 3.53
> 21 6 35 Spring 3.22
> 21 6 35 Spring 2.95
> I just want the price relevant to the quantity - eg in eaxample above, 35
> Springs qualifies for >30 price of 3.22.
> Appreciate any help!
> thansk,
> NEIL
>|||my pleasure, glad it helped