Jump to content

Recommended Posts

  • Replies 55
  • Created
  • Last Reply

Top Posters In This Topic

@sam,

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?

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..?

@Sam123,

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
FROM
pp_watchlist w
INNER JOIN pp_user u USING (uid)
INNER JOIN
   (
   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;

 

@Muddy

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.

yeah, thought that was it Barand, and keeping it simple is never a bad thing. I just thought that it was the discount price, not the ARP, that had to hit within the price watch ranges. To be honest this cURL problem I'm having is pickling my brain :'( So'm i'm gonna take a back seat for a while.

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.

Guest
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.