Select group of rows consisting of exactly one specific item-CodePudding

Am working on one analysis where I want to get a certain product that was bought in my item table. Let's say I have this data:

ProductId	ProductName	OrderId
1	Tshirt	1
4	Lipstick	1
4	Lipstick	2
6	Jeans	3
4	Lipstick	3
4	Lipstick	4
1	Tshirt	5
6	Jeans	5
4	Lipstick	5

How to get the complete Order that has a purchase of only Lipstick?

Output should be like this:

ProductId	ProductName	OrderId
4	Lipstick	2
4	Lipstick	4

CodePudding user response：

One approach to get a list of orders that include only lipstick:

SELECT OrderId
FROM   Item
GROUP
    BY OrderId
HAVING Min(ProductName) = 'Lipstick'
AND    Max(ProductName) = 'Lipstick'
;

CodePudding user response：

You can check if for any order item, no other item exists:

SELECT *
FROM t AS t1
WHERE ProductName = 'Lipstick'
AND NOT EXISTS (
    SELECT *
    FROM t AS t2
    WHERE t2.OrderId = t1.OrderId
    AND t2.ProductName <> 'Lipstick'
)

Explanation: the outer query selects all lipstick order items. This will return order #1, 2, 3, 4 and 5. Lets call these rows lipstick order items.

The sub query then, for each row in lipstick order items, selects related order items (t2.OrderId = t1.OrderId) that do not contain lipstick (2.ProductName <> 'Lipstick'). If no such row exists then the lipstick order item is unique.

CodePudding user response：

Another option, using conditional aggregation

SELECT i.OrderId
FROM Item i
GROUP BY
  i.OrderId
HAVING COUNT(CASE WHEN ProductName <> 'Lipstick' THEN 1 END) = 0;

CodePudding user response：

Retrieve only those product where previous row product and current product are same. That's why using lag() function for getting previous rows product.

SELECT t.ProductId, t.ProductName, t.OrderId 
FROM (SELECT *
           , LAG(ProductId) OVER (ORDER BY OrderId) prev_product
      FROM item) t
WHERE t.ProductId = t.prev_product
    AND t.ProductName = 'Lipstick'

Another way using previous and current row order

SELECT t.ProductId, t.ProductName, t.OrderId 
FROM (SELECT *
           , LAG(OrderId) OVER (ORDER BY OrderId) prev_order
      FROM item) t
WHERE t.OrderId != t.prev_order
    AND t.ProductName = 'Lipstick'

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=8cf359bb9bd73fd9fc9b4726117ff872

As per data retrieve even orderid.

SELECT *
FROM item
WHERE ProductName = 'Lipstick'
    AND OrderId % 2 = 0

N.B.: But I recommend first option only.