SQL Ordered Products with Discount Codes
Posted on 2016-08-24
We have these tables in a SQL 2008 database:
Orders and OrderedProducts have a relationship of one to many. OrderedProducts contains all the products from each order. In the OrderedProducts table there is a field where a discount code can be entered.
The Discounts table has a series of records that consist of the ProductName and the DiscountCode. Usually, there is a unique DiscountCode per each ProductName but if we have promotions where they need to buy 2 or 3 products in the same order, they will share the same DiscountCode.
This is an example of how records in the DiscountCode table would look like
ID Product DiscountCode
11 WidgetA TwENTYOFF
12 WidgetB FIFTEENOFF
13 WidgetC TwENTYOFF
14 WidgetD TEN0FF
And this would be some orders
OrderID EntryDate Purchaser
2929 8/12/16 Jane Doe
2930 8/13/16 John Smith
2931 8/15/16 Jay Lee
And here are their Ordered Products with their discounts (note OPID 4285 has a wrong discount because someone tried to use a better, but wrong discount for this product).
OPID OrderID Product DiscountCode
4283 2929 WidgetA TwENTYOFF
4284 2929 WidgetC TwENTYOFF
4285 2930 WidgetD TwENTYOFF
4286 2931 WidgetB FIFTEENOFF
How can I get a list of orders containing records where users entered the correct discount and all products were purchased for that discount code? For example, this list should contain
OrderID EntryDate Purchaser Product DiscountCode
2929 8/12/16 Jane Doe WidgetA TWENTYOFF
2929 8/12/16 Jane Doe WidgetC TWENTYOFF
2931 8/15/16 Jay Lee WidgetB FIFTEENOFF
Order2930 should not be included because even though John Smith entered a product code, it was the wrong one. I also expect this records to show multiple lines if a DiscountCode has more than one product that needs to be purchased with this code like the first two records.