CODEWITHSUNDEEP
sqlsql-server
Acorian0
Acorian0

Reputation: 115

Find Orders with exact number of items count - SQL Server

First of all thanks for your help.

I have 2 tables, one with orders and the other one with items bought.

Example:

ORDER:

ID 
---
1
2
3
4

ORDER_ITEMS:

ID | ORDER_ID | ITEM_ID
---+----------+---------
1  |    1     |   1
2  |    1     |   1
3  |    1     |   2
4  |    1     |   3
5  |    2     |   1
6  |    2     |   2
7  |    2     |   3
8  |    3     |   1
9  |    3     |   1
10 |    3     |   2
11 |    3     |   3

In this case all orders have the same items, but only 2 of them have the same amount of each item.

What I need is that for a given input of ITEM_ID I can get the ORDER_ID.

Example:

I have the following query to get the order, item, count:

SELECT 
    O.ORDER_ID, I.ITEM_ID, count(I.ITEM_ID) 
FROM 
    ORDER AS O
INNER JOIN 
    ORDER_ITEM AS I ON O.ORDER_ID = I.ORDER_ID 
GROUP BY 
    O.ORDER_ID, I.ITEM_ID
ORDER BY 
    O.ORDER_ID

With the following output:

ORDER_ID | ITEM_ID  | COUNT
---------+----------+------
    1    |    1     |   2
    1    |    2     |   1
    1    |    3     |   1
    2    |    1     |   1
    2    |    2     |   1
    2    |    3     |   1
    3    |    1     |   2
    3    |    2     |   1
    3    |    3     |   1

This is an example input and how i treat it, function just splits the string by ','

    DECLARE @ITEM_ID VARCHAR(255)
    SET @ITEM_ID = '1,1,2,3'
    SELECT DISTINCT(R.sID), COUNT(R.sID) FROM CommaSeparatedToString(@ITEM_ID ) AS R 
GROUP BY R.sID

   sID  |  COUNT
    1   |   2 
    2   |   1
    3   |   1

With what I already have, how can I achieve the desired output? In this case it would be orders 1 and 3.

Thanks again.

Upvotes: 0

Views: 673

Answers (3)

EricZ
EricZ

Reputation: 6205

Since you may have duplication items in the same order, you could add a row number as identifier 

WITH order AS (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) as rn from CommaSeparatedToString(@ITEM_ID )) 
,
i AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY [ORDER_ID] ORDER BY [ITEM_ID] ASC) as rn from ORDER_ITEMS)
SELECT  i.ORDER_ID
FROM  i
LEFT JOIN order o
  ON o.ID = i.ITEM_ID AND i.rn = o.rn
GROUP BY i.ORDER_ID
HAVING 
    COUNT(i.ID) = COUNT(o.rn)  -- ORDER_ITEMS match every order in the order list
  AND 
    COUNT(i.ID) = (SELECT COUNT(*) from order) -- ORDER_ITEMS have exact number of the order list

Upvotes: 1

James Casey
James Casey

Reputation: 2507

Starting from where you have got - my #counts table is your count by order and item, my #findme is the output of your last query representing what you are searching for.

create table #counts
(
    order_id int,
    item_id int,
    cnt int
);

insert into #counts values
   (1, 1 ,2),
   (1, 2 ,1),
   (1, 3 ,1),
   (2, 1 ,1),
   (2, 2 ,1),
   (2, 3 ,1),
   (3, 1 ,2),
   (3, 2 ,1),
   (3, 3 ,1);

create table #findme
(
    item_id int,
    cnt int
);

insert into #findme values
  (1,2),
  (2,1),
  (3,1);

-- get count of different items per order
select order_id,
  count(*) as cnt
into #itemcnt
from #counts
group by order_id

-- now join our search query and see how many rows match on both item and count
select order_id, count(*) cnt
into #found
from #findme
inner join #counts 
    on #findme.item_id = #counts.item_id
   and #findme.cnt = #counts.cnt
group by order_id

-- now join this to see where number of matches = number of different items
select distinct #found.order_id
from #found
inner join #itemcnt on #found.cnt = #itemcnt.cnt

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you want to provide input as "1,1,2,3", then the simplest solution is to aggregation the values:

select o.*
from (select o.*,
             stuff( (select ',' + cast(oi.item_id as varchar(255))
                     from order_items oi
                     where oi.order_id = o.id
                     order by oi.item_id
                     for xml path ('')
                    ), 1, 1, '') as items
      from orders o
     ) o
where items in ('1,1,2,3');

Upvotes: 3

Related Questions