Jump to content

Calculating Product Offer


Recommended Posts

  • Replies 55
  • Created
  • Last Reply

Top Posters In This Topic


The prodID in prod and whishlist tables are different.

watchlist should get the prodID from pp_prod and every entry in the product table should have a unique prodID, that's why it was made as a primary key. If the features are different between products then the product is different and it goes in as a different item. You need to have unique identifiers for each record in order to maintain and query the data successfully. wishlist should use the prodID to refference the product from prod, If you duplicate the prodID in prod then bad things will happen.


@barand, Liking the query you posted, It looks to me that you are using the ARP (which I ment to be Actual Retail Price) rather than calculating the discount price, is that right?

Link to comment
Share on other sites

The problem what i am facing is that , the user is saying i am ready to pay this much amount for one product. May be that time there will not be any product added by the retailer. If after sometime the retailer added the 2 products with different price. eg. Samsung 30 LED and Samsung 40 HD and he is setting the offer if 3 customers request product of this price range he will give it for offerprice.


So how do we do that..? Here i got confused. Can you please advice..?

Link to comment
Share on other sites


Does this fit the requirement?

SELECT DISTINCT u.uid, u.e_mail, cnt.mfid, cnt.prodid,
   cnt.name, cnt.total, cnt.arp, cnt.discount_price
pp_watchlist w
INNER JOIN pp_user u USING (uid)
   SELECT p.mfid, p.prodid, p.ARP, p.name, COUNT(w.uid) as total,
    (SELECT MIN(discount_as_price)
    FROM pp_offers
    WHERE prodID = p.prodID AND no_required <= COUNT(w.uid)
    ) as discount_price
   FROM pp_watchlist w
   INNER JOIN pp_prod p ON w.prodid = p.mfid
   WHERE p.ARP BETWEEN w.priceLow AND w.priceHigh
   GROUP BY p.prodID
   ) as cnt ON w.prodID = cnt.mfid
ORDER BY mfid, uid;



I use ARP to see if it is within the low-high range in watchlist - as required AFAIK

Also I am not checking the three types of discount (as your CASE did). Trying to keep it simple as only the one type is being used.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.