Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Ordered Products with Discount Codes

Posted on 2016-08-24
3
Medium Priority
?
81 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 1000 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 500 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 500 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

577 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