Solved

SQL Ordered Products with Discount Codes

Posted on 2016-08-24
3
45 Views
Last Modified: 2016-08-24
Hello,

We have these tables in a SQL 2008 database:  

- Orders
- OrderedProducts
- Discounts

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


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

OrderedProducts
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
the following:

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.  


Thanks.
0
Comment
Question by:TheUndecider
3 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 250 total points
ID: 41769323
DECLARE @Discount TABLE
(
	ID				INT,
	Product			VARCHAR(50),
	DiscountCode	VARCHAR(50)
);

INSERT @Discount (ID, Product, DiscountCode)
VALUES
	(11, 'WidgetA', 'TwENTYOFF'),
	(12, 'WidgetB', 'FIFTEENOFF'),
	(13, 'WidgetC', 'TwENTYOFF'),
	(14, 'WidgetD', 'TEN0FF');

DECLARE @Order TABLE
(
	OrderID			INT,
	EntryDate		DATETIME,
	Purchaser		VARCHAR(50)
);

INSERT @Order (OrderID, EntryDate, Purchaser)
VALUES
	(2929, '8/12/16', 'Jane Doe'),
	(2930, '8/13/16', 'John Smith'),
	(2931, '8/15/16', 'Jay Lee');

DECLARE @OrderProduct TABLE
(
	OPID			INT,
	OrderID			INT,
	Product			VARCHAR(50),
	DiscountCode	VARCHAR(50)
);

INSERT @OrderProduct (OPID, OrderID, Product, DiscountCode)
VALUES
	(4283, 2929, 'WidgetA', 'TwENTYOFF'),
	(4284, 2929, 'WidgetC', 'TwENTYOFF'),
	(4285, 2930, 'WidgetD', 'TwENTYOFF'),
	(4286, 2931, 'WidgetB', 'FIFTEENOFF');

WITH cteDiscountOrder AS
(
	SELECT O.OrderID
	FROM @Order AS O
	INNER JOIN @OrderProduct AS OP
		ON O.OrderID = OP.OrderID
	LEFT OUTER JOIN @Discount AS D
		ON OP.Product = D.Product
		AND OP.DiscountCode = D.DiscountCode
	GROUP BY O.OrderID
	HAVING COUNT(O.OrderID) = COUNT(D.ID)
)
SELECT O.*, OP.*
FROM cteDiscountOrder AS cte
INNER JOIN @Order AS O
	ON cte.OrderID = O.OrderID
INNER JOIN @OrderProduct AS OP
	ON O.OrderID = OP.OrderID

Open in new window

0
 
LVL 1

Assisted Solution

by:Helen Ramsden
Helen Ramsden earned 125 total points
ID: 41769325
Hi, does this help?

SELECT dbo.Orders.OrderID, dbo.Orders.EntryDate, dbo.Orders.Purchaser, dbo.OrderedProducts.Product, dbo.OrderedProducts.DiscountCode
FROM     dbo.Orders INNER JOIN
                  dbo.OrderedProducts ON dbo.Orders.OrderID = dbo.OrderedProducts.OrderID
WHERE  (dbo.OrderedProducts.Product + dbo.OrderedProducts.DiscountCode) IN
                      (SELECT Product + DiscountCode
                       FROM      dbo.DiscountCode)
0
 
LVL 10

Assisted Solution

by:Marcjev
Marcjev earned 125 total points
ID: 41769345
select O.OrderId, O.EntryDate, O.Purchaser, P.Product, p.DiscountCode, *
from orders O
join OrderedProducts P on p.OrderId = O.OrderId
join (
	select	P.DiscountCode, count(*) NumberOfProducts	
	from orders O
	join OrderedProducts P on p.OrderId = O.OrderId
	join Discounts C on C.DiscountCode = P.DiscountCode and C.Product = P.Product
	group by p.DiscountCode ) as no on no.DiscountCode = p.DiscountCode
join (
		select discounts.DiscountCode, Discounts.id, Discounts.Product, no.NumberOfProducs 
		from discounts 
		join ( 
				select discountcode, count(*) NumberOfProducs 
				from Discounts 
				group by discountcode
			) as no on no.DiscountCode = discounts.DiscountCode
	) as no2 on no2.product = P.product and no2.DiscountCode = p.DiscountCode and no.NumberOfProducts = no2.NumberOfProducs

Open in new window


Remove the  ,* to see the output you requested.

(also try 2 times selling widgetA with correct discountcode but no widgetC. Should it not showup as correct orders?)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 51
SQL Sum of items in two tables not equal. 5 49
SQL Server Express or Standard? 5 29
Applying Roles in Common Scenarios 3 18
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question