March 7, 2022 at 8:21 pm
Here is the situation, I want to return the results of the following:
A customer may purchase any number of items per order. I want to return a result set of where any one of three specific items were ordered and nothing more. The example SQL code is below without an answer to the question. I want to list out all orders where shoes were purchased or Shoe laces were purchased or Shoe cleaner was purchased or any combination of the three were purchased and the orders may not have any other products with it. The answer should not explicitly exclude the other four options as this is just an example and the actual list contains 1000's or products. Any help would be greatly appreciated.
CREATE TABLE dbo.Products (ID INT NOT NULL, ProductDesc VARCHAR(25) NOT NULL)
INSERT INTO dbo.Products(ID, ProductDesc)
VALUES (1,'Shoes'),(2, 'Shoe Laces'),(3,'Shoe Cleaner'),(4,'Polo Shirt'),(5,'Tee Shirt'),(6,'Pants'),(7,'Belt');
CREATE TABLE dbo.Customers(ID INT NOT NULL, CustName VARCHAR(25) NOT NULL)
INSERT INTO dbo.Customers(ID, CustName)
VALUES (1,'Adam'),(2,'Barry'),(3,'Charlie'),(4,'Danielle'),(5,'Erik'),(6,'Frank'),(7,'Gary');
CREATE TABLE dbo.Orders (ID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL)
INSERT INTO dbo.Orders(ID, CustomerID, OrderDate)
VALUES (1,1,GETDATE()-7),(2,2,GETDATE()-6),(3,3,GETDATE()-5),(4,4,GETDATE()-4),(5,5,GETDATE()-3),(6,6,GETDATE()-2),(7,7,GETDATE()-1);
CREATE TABLE dbo.OrderProducts (ID INT NOT NULL, OrderID INT NOT NULL, ProductID INT NOT NULL)
INSERT INTO dbo.OrderProducts(ID, OrderID, ProductID)
VALUES(1,1,1),
(2,1,2),
(3,1,3),--Include order as it has the criteria needed (Order 1 included)
(4,2,1),
(5,2,2),
(6,2,3),
(7,2,7),--Exclude because this is not part of the set (Order 2 excluded)
(8,3,1),--Include (Order 3 included)
(9,4,3),
(10,4,4),--Exclude because this is an extra item (Order 4 excluded)
(11,5,5)--Exclude because does not contain requested items
-- Looking to return orders where a customer purchased Shoes, Shoe Laces or Shoe Cleaner or any combination of these and NOTHING ELSE.
-- This is a limited list of products for demonstration purposes but the actual list is 1000+ items.
-- Results should only show OrderID 1 and 3
--All Orders
SELECT C.CustName, O.ID OrderID, O.OrderDate, P.ProductDesc
FROM dbo.Orders O INNER JOIN dbo.OrderProducts OP ON O.ID = OP.OrderID
INNER JOIN dbo.Customers C ON O.CustomerID = C.ID
INNER JOIN dbo.Products P ON OP.ProductID = P.ID
-- CTE of Products that I am looking for
;WITH OrderGroupOnly AS (
SELECT ID
FROM dbo.OrderProducts OP
WHERE OP.ProductID IN (1,2,3)
)
SELECT C.CustName, O.ID OrderID, O.OrderDate, P.ProductDesc
FROM dbo.Orders O INNER JOIN dbo.OrderProducts OP ON O.ID = OP.OrderID
INNER JOIN dbo.Customers C ON O.CustomerID = C.ID
INNER JOIN dbo.Products P ON OP.ProductID = P.ID
INNER JOIN OrderGroupOnly OGO ON OP.ID = OGO.ID
--How do I exclude orders 2 and 4?
/*
--Drop objects when complete
DROP TABLE dbo.Products
DROP TABLE dbo.Customers
DROP TABLE dbo.Orders
DROP TABLE dbo.OrderProducts
*/
Thank you in advance if someone has the answer
Owen White
March 7, 2022 at 8:56 pm
Maybe this?
WITH OrderGroupOnly AS (
SELECT OrderID
FROM OrderProducts
WHERE ProductID IN (1,2,3)
EXCEPT
SELECT OrderID
FROM OrderProducts
WHERE ProductID NOT IN (1,2,3)
)
SELECT C.CustName, O.ID OrderID, O.OrderDate, P.ProductDesc
FROM dbo.Orders O
INNER JOIN dbo.OrderProducts OP ON O.ID = OP.OrderID
INNER JOIN dbo.Customers C ON O.CustomerID = C.ID
INNER JOIN dbo.Products P ON OP.ProductID = P.ID
INNER JOIN OrderGroupOnly OGO ON OP.OrderID = OGO.OrderID
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 7, 2022 at 8:59 pm
You have a list of all Orders that have at least one of three Items included.
You wish to exclude all Orders that have an Item that is not one of the three on your list. In other words, exclude any Order ID returned in the following query:
SELECT OrderID FROM dbo.OrderProducts OP2 WHERE OP.ProductID NOT IN (1,2,3)
You can do this with a correlated subquery.
Add to the WHERE clause:
AND NOT EXISTS(SELECT * FROM dbo.OrderProducts OP2 WHERE O.OrderID = OP2.OrderID AND OP.ProductID NOT IN (1,2,3))
Eddie Wuerch
MCM: SQL
March 7, 2022 at 9:03 pm
You would want a nonclustered index on dbo.OrderProducts ( OrderID, ProductID ) to support this query.
--using variables to make the code easier to use and maintain
--may specify 1, 2 or all 3 products
DECLARE @Product_1 varchar(25)
DECLARE @Product_2 varchar(25)
DECLARE @Product_3 varchar(25)
SET @Product_1 = 'Shoes'
SET @Product_2 = 'Shoe Laces'
SET @Product_3 = 'Shoe Cleaner'
SELECT C.CustName, O.ID OrderID, O.OrderDate, P.ProductDesc
FROM dbo.Orders O INNER JOIN dbo.OrderProducts OP ON O.ID = OP.OrderID
INNER JOIN dbo.Customers C ON O.CustomerID = C.ID
INNER JOIN dbo.Products P ON OP.ProductID = P.ID
WHERE O.ID IN (
SELECT op.OrderID
FROM dbo.OrderProducts op
LEFT OUTER JOIN (
SELECT ID
FROM dbo.Products
WHERE ProductDesc IN (@Product_1, @Product_2, @Product_3)
) AS p ON p.ID = op.ProductID
GROUP BY op.OrderID
HAVING SUM(CASE WHEN p.ID IS NULL THEN 1 ELSE 0 END) = 0
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2022 at 1:19 pm
Scott and Michael,
Thank you both for taking the time to help with this query. I guess I never tried this because my thought process would be that this query would include the records that had the 1, 2 or 3 and would still include the orders where there were additional items. After reviewing your code, it makes sense that it finds orders where there is anything other than 1, 2 and 3 thus excluding any order that has additional items.
Thank you both!
Owen White
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply