Need help writing an SQL to pull my order coupons

[nickmccally]

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

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)

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!

[guido2004]

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?