Select number of times 2 specific values show up for one record id

Question

I'm not sure my title above makes sense, so I'll try to clarify. I'm using SQL Server.

I have a table called Orders that looks like this (truncated):

Order_Number | SKU
           1 | abc
           1 | def
           1 | ghi
           2 | abc
           2 | ghi
           3 | def
           4 | abc
           4 | def
           4 | ghi
           5 | abc

I need to find all Order Numbers that include SKUs abc and ghi. So if I ran the query on the above table, it would return:

Order_Number
           1
           2
           4

If the Order Number only includes abc or ghi, not both, I don't want to return the Order Number. If it includes extra SKUs, such as def, that is OK, I still want to return the record. I don't need to see all the SKUs in the output, just the Order Number. Does that make sense?

Thanks in advance!

Answer 1

You can do this in two different ways:

1) Use EXISTS

SELECT
    Order_Number
FROM
    (-- Get the list of Order_Number
    SELECT DISTINCT
        Order_Number
    FROM
        Orders
    ) AS onr
WHERE
    -- Check that there is an 'abc' associated
    EXISTS
    (
    SELECT *
    FROM Orders os1
    WHERE os1.Order_Number = onr.Order_Number and os1.SKU = 'abc'
    )
    AND
    -- and also Check that there is an 'ghi' associated
    EXISTS
    (
    SELECT *
    FROM Orders os2
    WHERE os2.Order_Number = onr.Order_Number and os2.SKU = 'ghi'
  )
ORDER BY
    onr.Order_Number
GO

| Order_Number |
| -----------: |
|            1 |
|            2 |
|            4 |

2) Substitute the EXISTS by equivalent JOINS with join condition including the desired value of SKU:

SELECT
    onr.Order_Number
FROM
    (SELECT DISTINCT 
        Order_Number 
    FROM 
        Orders
    ) AS onr
    JOIN Orders AS sk1 ON sk1.Order_Number = onr.Order_Number AND sk1.SKU = 'abc'
    JOIN Orders AS sk2 ON sk2.Order_Number = onr.Order_Number AND sk2.SKU = 'ghi'
ORDER BY
    onr.Order_Number    
GO

You get exactly the same result. Which version you use depends on which one you find clearer to understand. If your tables are really big, test whether one gives a better execution plan.

You can check all the statments at dbfiddle here

Answer 2

Assuming that there is a unique constraint on (Order_Number, SKU):

SELECT
    sk1.Order_Number
FROM
    dbo.Orders AS sk1
  JOIN 
    dbo.Orders AS sk2 
  ON  sk1.Order_Number = sk2.Order_Number
  AND sk1.SKU = 'abc'
  AND sk2.SKU = 'ghi' ;

Tested at dbfiddle.uk. Note how with an index on (SKU, Order_Number) the execution plan starts with 2 index seeks and doesn't scan the whole table.

Answer 3

You could also use window functions which are usually faster,to get OrderNumbers that contain two rows, meaning contain both 'ghi' & 'abc'

 ;WITH CTE AS(
 select Order_Number ,SKU
 from Orders
 where SKU ='abc'

 UNION
  select Order_Number ,SKU
 from Orders
 where SKU ='ghi')
 ,
WindowFunction AS(
 select  Order_Number,ROW_NUMBER() OVER (PARTITION BY Order_Number ORDER BY (select null)) as RowNumber from CTE

 )
 select Order_Number from WindowFunction
where RowNumber = 2

Hope it helps!

Answer 4

If you might have 3 or more SKUs, and don't want to keep modifying your statement to accommodate the current number:

DROP TABLE #OrderItems;
GO
DROP TABLE #SKU_List;
GO

CREATE TABLE #OrderItems (order_id int, SKU varchar(10));

INSERT INTO #OrderItems
VALUES (1,'abc')
      ,(1,'def')
      ,(1,'ghi')
      ,(2,'abc')
      ,(2,'ghi')
      ,(3,'def')
      ,(4,'abc')
      ,(4,'def')
      ,(4,'ghi')
      ,(5,'abc')
;

-- Option 3 - Scalable to more SKUs
--   1. put SKUs in a temp table
--   2. find the number of SKUs that have to be matched
--   3. SELECT order_ids matching any of those SKUs
--   4. GROUP order_ids, COUNT number of DISTINCT SKUs, keep if same as number of SKUS to  match.

CREATE TABLE #SKU_List (SKU varchar(10));

INSERT INTO #SKU_List VALUES ('abc'), ('ghi');

WITH Vars (SKU_Count) AS
     (SELECT COUNT(*) FROM #SKU_List)
SELECT oi.order_id
  FROM #OrderItems oi
         INNER JOIN #SKU_List sku ON (oi.SKU = sku.SKU)
         CROSS JOIN Vars
 GROUP BY oi.order_id, Vars.SKU_Count
HAVING COUNT(DISTINCT oi.SKU) = Vars.SKU_Count
 ORDER BY order_id
;