Stored procedure to get cutomer Ids on the basis of comma seperated value passed-CodePudding

Items Table:

id	item_name	code	cust_id
1	Mango	111	u1
2	Milk	112	u2
3	Chocolate	113	u3
4	Milk	112	u1
5	Mango	111	u2
6	Berry	114	u3
7	Chocolate	113	u1
8	Berry	114	u2
9	Ice-cream	114	u3
10	Mango	111	u4

These is the customer table and I have to write an stored procedure in SQL Server to find the cust_id who have buy all items passed in comma separated values like 'Mango,Milk,Chocolate'

CREATE PROCEDURE Items_find
    @items_value nvarchar(max)
AS
BEGIN
END

EXEC Items_find 'Mango,Milk,Chocolate'  
EXEC Items_find 'Mango,Milk'

Stored procedure should return cust_id = u1 if 'Mango,Milk' then output should be cust_id=u1, u2 or if 'Mango' then output is cust_id=u1, u2, u3

I have tried to filter out the cust_id w.r.t. to the count of items passed in comma-separated but after that I am not able find the cust_id who buys specifically these items

CodePudding user response：

One solution is to count first how many words are in your search,
and then select all rows that contain your search and use the count to keep only those with the same group by count

Here is an example

declare @Items table (id int, item_name varchar(50), code int, cust_id varchar(10))
insert into @Items values (1, 'Mango', 111, 'u1'),
(2, 'Milk', 112, 'u2'), (3, 'Chocolate', 113, 'u3'), (4, 'Milk', 112, 'u1'), (5, 'Mango', 111, 'u2'),
(6, 'Berry', 114, 'u3'), (7, 'Chocolate', 113, 'u1'), (8, 'Berry', 114, 'u2'), (9, 'Ice-cream', 114, 'u3'),
(10, 'Mango', 111, 'u4')

declare @search varchar(50) = 'Mango,Milk'
declare @cnt int = (select len(@search) - len(replace(@search, ',', ''))   1)

select cust_id
from   @Items
where  @search like '%'   item_name   '%'
group by cust_id
having count(1) = @cnt

this will return

cust_id
u1
u2

And if you want it in one string, you can use string_agg for that

declare @search varchar(50) = 'Mango,milk'
declare @cnt int = (select len(@search) - len(replace(@search, ',', ''))   1)

select 'cust_id = '  
( select string_agg(t.cust_id, ', ') 
          from   ( select top 100000
                          i.cust_id
                   from   @Items i
                   where @search like '%'   i.item_name   '%'
                   group by i.cust_id
                   having count(1) = @cnt
                  ) t
 ) as Result

this will return

cust_id = u1, u2

CodePudding user response：

DECLARE @InProducts VARCHAR(100);
--SET @InProducts='Mango,Milk,Chocolate';
SET @InProducts='Mango,Milk';

WITH CTE(id, item_name, code, cust_id) AS
(
  SELECT 1,'MANGO',111,'U1'
    UNION ALL
  SELECT 2,'MILK',112,'U2'
    UNION ALL
  SELECT 3,'CHOCOLATE',113,'U3'
   UNION ALL
  SELECT 4,'MILK',112,'U1'
   UNION ALL
  SELECT 5,'MANGO',111,'U2'
   UNION ALL
  SELECT 6,'BERRY',114,'U3'
   UNION ALL
  SELECT 7,'CHOCOLATE',113,'U1'
   UNION ALL
  SELECT 8,'BERRY',114,'U2'
   UNION ALL
  SELECT 9,'ICE-CREAM',115,'U3'
   UNION ALL
  SELECT 10,'MANGO',111,'U4'
)
SELECT Z.CUST_ID FROM
 (
    SELECT C.*FROM CTE AS C
    JOIN string_split(@InProducts,',')X ON C.item_name=X.value
 )Z 
GROUP BY Z.cust_id HAVING COUNT(Z.CUST_ID)=
(
    SELECT COUNT(X.VALUE) FROM string_split(@InProducts,',')X
)

Hope, you can use something like this