SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Aug 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help writing an SQL to pull my order coupons

    Hey guys, I am writing a function to calculate an order total. I am looking at an SQL to pull this information and not hack it up with php like I currently have.

    Each order has order offers for each item. Since we commonly have order amendments there might be more than one version of an order offer. The highest offer version is the data we use in calculating a price.

    Now, here is the SQL I use to pull all of the order offers for the individual orders:

    Code:
    SELECT * FROM order_offer offer 
    			WHERE offer.order_id = ?
    			AND offer.offer_version IN (SELECT MAX(offer_version) FROM order_offer WHERE offer_id = offer.offer_id)
    			"
    Now, I need to create a similar SQL to pull the coupons for each order offer. Each order offer MAY have more than one coupon or may not have them at all.

    Here is the SQL I use to find valid order offers that CAN have coupons applied (if it helps)
    Code:
    SELECT * FROM order_offer offer 
    			WHERE offer.order_id = ?
    			AND offer.offer_version IN (SELECT MAX(offer_version) FROM order_offer WHERE offer_id = offer.offer_id)
    			AND offer.offer_id NOT IN
    			(
    				SELECT o.offer_id
    				FROM order_offer o LEFT JOIN order_coupons c ON o.offer_id = c.offer_id 
    				WHERE o.offer_version = offer.offer_version
    				AND o.offer_version = c.offer_version
    				AND c.order_coupon_stackable = 0
    				AND o.order_id = offer.order_id
    			)"
    Thank you for the help!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Now, here is the SQL I use to pull all of the order offers for the individual orders:
    Doesn't that query return only 1 row? The max order offer version?

    And I don't quite understand your question. Do you want to have 1 query instead of those 2?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •