none
List of customers with a set of orders RRS feed

  • Question

  • This is just an example, say I've got the following

    CREATE TABLE #Customer(Id INT, FullName VARCHAR(20)); CREATE TABLE #Item(Id INT, Descr VARCHAR(20)); CREATE TABLE #Order(Id INT, CustomerId INT, ItemId INT); INSERT INTO #Customer (Id, FullName) VALUES (1,'Wild'), (2,'Bill'); INSERT INTO #Item (Id, Descr) VALUES (1,'Socks'), (2,'Shoes'), (3, 'Pants'); INSERT INTO #Order (Id, CustomerId, ItemId) VALUES (1,1,1), --Wild orders socks

    (4,1,3), --Wild orders pants

    (2,2,1), --Bill orders socks (3,2,2); --Bill orders shoes


    I would like to return Bill because he ordered socks and shoes but not Wild because he ordered socks and pants.  Initially I thought about intersecting the order table with itself, but that is not going to scale very well.  As my data grows I may want to find orders with 3 or more specific items.


    • Edited by Wild.Bill Friday, July 19, 2019 4:02 PM Updating test data
    Friday, July 19, 2019 2:47 PM

All replies

  • CREATE TABLE #Customer(Id INT, FullName VARCHAR(20));
    CREATE TABLE #Item(Id INT, Descr VARCHAR(20));
    CREATE TABLE #Order(Id INT, CustomerId INT, ItemId INT);
    
    INSERT INTO #Customer (Id, FullName) VALUES (1,'Wild'), (2,'Bill');
    INSERT INTO #Item (Id, Descr) VALUES (1,'Socks'), (2,'Shoes');
    INSERT INTO #Order (Id, CustomerId, ItemId) VALUES 
    (1,1,1), --Wild orders socks
    (2,2,1), --Bill orders socks
    (3,2,2); --Bill orders shoes
    Select Id, FullName
    from #Customer 
    Where Id IN (
    Select CustomerId  from #Order
    group by CustomerId
    Having(count(distinct ItemId)>1)
    )
    
    drop table #Customer,#Order,#Item

    Friday, July 19, 2019 2:54 PM
    Moderator
  • Technically this works per my requirement so I'll update the test data.  I don't need everyone with multiple items,  I need customers that ordered specific combinations of items.

    Friday, July 19, 2019 4:00 PM
  • CREATE TABLE #Customer(Id INT, FullName VARCHAR(20));
    CREATE TABLE #Item(Id INT, Descr VARCHAR(20));
    CREATE TABLE #Order(Id INT, CustomerId INT, ItemId INT);
    
    INSERT INTO #Customer (Id, FullName) VALUES (1,'Wild'), (2,'Bill');
    INSERT INTO #Item (Id, Descr) VALUES (1,'Socks'), (2,'Shoes');
    INSERT INTO #Order (Id, CustomerId, ItemId) VALUES 
    (1,1,1), --Wild orders socks
    (4,1,3), --Wild orders pants
    (2,2,1), --Bill orders socks
    (3,2,2); --Bill orders shoes
    Select Id, FullName
    from #Customer c
    Where Exists(
    Select CustomerId  from #Order o join #Item i on i.Id=o.ItemId
    WHERE o.CustomerId= c.Id and i.Descr='socks') 
    AND 
    Exists(
    Select CustomerId  from #Order o join #Item i on i.Id=o.ItemId
    WHERE o.CustomerId=c.Id and i.Descr='shoes') 
    -- AND  for more contition if you want
     
    drop table #Customer,#Order,#Item

    Friday, July 19, 2019 4:28 PM
    Moderator
  • That's what I was thinking originally as well.  But like I stated, this won't scale.  I'll have this query for two items, but then I would have to create another for 3, 4, and so on.  Ideally I'm looking for a way to specify a list of items and when the orders contains that list, it returns the member.
    Saturday, July 20, 2019 4:27 PM
  • please show us table format result 

    we need to see first

    we are programmers

    Saturday, July 20, 2019 7:41 PM
  • (...) Ideally I'm looking for a way to specify a list of items and when the orders contains that list, it returns the member.

    Try

    -- code #1
    
    -- "specify a list of items"
    IF Object_Id ('tempDB..#Monitorado', 'U') is not null
      DROP TABLE #Monitorado;
    go
    
    CREATE TABLE #Monitorado (ItemId int unique);
    INSERT into #Monitorado values (1), (2);      -- socks and shoes
    
    declare @Qtd int;
    SELECT @Qtd= count(*) 
      from #Monitorado;
    
    --
    with 
    Comprador as (
    SELECT distinct CustomerId
      from [Order]
    ),
    Esperado as (
    SELECT C.CustomerId, M.ItemId
      from Comprador as C 
           cross join #Monitorado as M
    ),
    Comprado as (
    SELECT distinct O.CustomerId, O.ItemId
      from [Order] as O
           inner join #Monitorado as M on M.ItemId = O.ItemId
    )
    -- "it returns the member"
    SELECT C.CustomerId from Esperado as E inner join Comprado as C on C.CustomerId = E.CustomerId and C.ItemId = E.ItemId group by C.CustomerId having count(*) = @Qtd;

    --
    IF Object_Id ('tempDB..#Monitorado', 'U') is not null
      DROP TABLE #Monitorado;
    go


    It may not be the best solution, but it's a solution.

    ---

    Simplified version:

    -- code #2
    -- "specify a list of items"
    IF Object_Id ('tempDB..#Monitorado', 'U') is not null
      DROP TABLE #Monitorado;
    go
    
    CREATE TABLE #Monitorado (ItemId int unique);
    INSERT into #Monitorado values (1), (2);      -- socks and shoes
    
    declare @Qtd int;
    SELECT @Qtd= count(*) 
      from #Monitorado;
    
    --
    with 
    Comprado as (
    SELECT distinct O.CustomerId, O.ItemId
      from [Order] as O
           inner join #Monitorado as M on M.ItemId = O.ItemId
    )
    -- "it returns the member" SELECT CustomerId from Comprado group by CustomerId having count(*) = @Qtd;

    --
    IF Object_Id ('tempDB..#Monitorado', 'U') is not null
      DROP TABLE #Monitorado;
    go 
     

    Remember to mark/vote this answer if it helped you.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Edited by José Diz Sunday, July 21, 2019 10:08 AM
    Saturday, July 20, 2019 10:33 PM
  • Hi Wild,

    I add some sample data for test. Hope that could help. 

    IF OBJECT_ID('#Customer') IS NOT NULL drop table  #Customer
    IF OBJECT_ID('#Item') IS NOT NULL drop table  #Item
    IF OBJECT_ID('#Order') IS NOT NULL drop table  #Order
    CREATE TABLE #Customer(Id INT, FullName VARCHAR(20));
    CREATE TABLE #Item(Id INT, Descr VARCHAR(20));
    CREATE TABLE #Order(Id INT, CustomerId INT, ItemId INT);
    
    INSERT INTO #Customer (Id, FullName) VALUES (1,'Wild'), (2,'Bill'),(3,'AAA')
    INSERT INTO #Item (Id, Descr) VALUES (1,'Socks'), (2,'Shoes'), (3, 'Pants'),(4,'shirts')
    INSERT INTO #Order (Id, CustomerId, ItemId) VALUES 
    (1,1,1), 
    (4,1,3), 
    (2,2,1), 
    (3,2,2),
    (5,3,3),
    (6,2,4) 
    
    DECLARE @sql_str VARCHAR(8000)
    DECLARE @sql_col VARCHAR(8000)
    DECLARE @cte VARCHAR(8000)
    
    set @cte = 
    'select a.FullName,b.Descr,c.CustomerId from 
    #Customer a 
    join #Order c
    on a.Id= c.CustomerId
    join #Item b
    on b.Id= c.ItemId'
    
    SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([Descr]) FROM #Item
    SET @sql_str = '
    SELECT * FROM (
        SELECT [FullName],[Descr],[CustomerId] FROM ('+ @cte+ ') a )b
    	PIVOT (max([CustomerId]) FOR [Descr] IN ( '+ @sql_col +') ) AS pvt 
    '
    PRINT (@sql_str)
    EXEC (@sql_str)
    
    /*
    FullName             Socks       Shoes       Pants       shirts
    -------------------- ----------- ----------- ----------- -----------
    AAA                  NULL        NULL        3           NULL
    Bill                 2           2           NULL        2
    Wild                 1           NULL        1           NULL
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 22, 2019 5:50 AM