Results 1 to 2 of 2
May 9, 2013, 14:17 #1
- Join Date
- Aug 2012
- 0 Post(s)
- 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:
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) "
Here is the SQL I use to find valid order offers that CAN have coupons applied (if it helps)
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 )"