CODEWITHSUNDEEP
sql-server-2008t-sqlmany-to-many
Darthg8r
Darthg8r

Reputation: 12685

SQL Server : Many to Many

I have a many to many relationship, Orders, OrderProducts, and Products. I need a query that gives me a list of products that are NOT in ALL orders, but has been ordered.

______________________
|      ORDERS         |
_______________________
| OrderID | OrderDate |
| 1       | 1/2/2012  |
| 2       | 1/3/2012  |
| 3       | 1/4/2012  |
| 4       | 1/5/2012  |
| 5       | 1/6/2012  |

______________________
|      ORDERPRODUCTS  |
_______________________
| OrderID | PRODUCTID |
| 1       | 1         |
| 1       | 2         |
| 2       | 1         |
| 2       | 2         |
| 2       | 3         |
| 2       | 4         |
| 3       | 1         |
| 3       | 5         |
| 4       | 1         |
| 5       | 1         |

__________________________
|      PRODUCTS           |
__________________________
| PRODUCTID | PRODUCTNAME |
| 1         | Widget 1    |
| 2         | Widget 2    |
| 3         | Widget 3    |
| 4         | Widget 4    |
| 5         | Widget 5    |
| 6         | Widget 6    |

In the provided example, notice that product 1 is in all orders and product 6 is not ordered at all.

I need a query that returns Products 2, 3, 4, and 5.

Also keep in mind that while there aren't many products, there are a few hundred thousand orders in the live database.

Upvotes: 3

Views: 707

Answers (3)

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6783

SELECT
  DISTINCT
  PossibleOrderProducts.PRODUCTID
FROM
  (
  SELECT
    Orders.ORDERID,
    Products.PRODUCTID
  FROM
    ORDERS Orders
    CROSS JOIN
      (
      SELECT DISTINCT PRODUCTID FROM ORDERPRODUCTS
      ) Products
  ) PossibleOrderProducts
  LEFT JOIN ORDERPRODUCTS ActualOrderProducts ON
    ActualOrderProducts.ORDERID = PossibleOrderProducts.ORDERID
    AND ActualOrderProducts.PRODUCTID = PossibleOrderProducts.PRODUCTID
WHERE
  ActualOrderProducts.ORDERID IS NULL

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

It's Saturday night, so this is probably not the most elegant, but here's one try:

DECLARE @OrderProducts TABLE(OrderID INT, ProductID INT);

DECLARE @Products TABLE(ProductID INT, ProductName VARCHAR(32));

INSERT @Products VALUES
(1,'Widget 1'),(2,'Widget 2'),
(3,'Widget 3'),(4,'Widget 4'),
(5,'Widget 5'),(6,'Widget 6');

INSERT @OrderProducts VALUES
(1,1),(1,2),(2,1),(2,2),(2,3),
(2,4),(3,1),(3,5),(4,1),(5,1);

SELECT p.ProductID, p.ProductName 
FROM @Products AS p
WHERE EXISTS -- had been ordered at least once
(
  SELECT 1 FROM @OrderProducts 
  WHERE ProductID = p.ProductID
)
AND EXISTS -- at least one order does NOT include it
(
  SELECT 1 FROM @OrderProducts AS o 
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM @OrderProducts AS o2 
    WHERE o2.OrderID = o.OrderID 
    AND o2.ProductID = p.ProductID
  )
);

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453618

SELECT PRODUCTID 
FROM ORDERPRODUCTS  
GROUP BY PRODUCTID 
HAVING COUNT(DISTINCT OrderID) < (SELECT COUNT(*) FROM ORDERS )

Upvotes: 4

Related Questions