advice on query

25 Aug 2005 3:47 PM

Neil Jarman

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

25 Aug 2005 4:05 PM

jason

well, 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

25 Aug 2005 4:07 PM

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))

25 Aug 2005 4:10 PM

Perayu

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

Show quote

"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 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
>
>
>

25 Aug 2005 5:45 PM

Neil Jarman

Thank you both sooooo much - I was really stuck with that one!!!

NEIL

Show quote

"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
>

25 Aug 2005 6:31 PM

jason

my pleasure, glad it helped