Sql-server – Find the customers who bought at least the products bought by customer with “name 4”

sql-server-2012

I have the following 3 tables:

  • Invoice {NrFact, Date, CodCl}
  • LinesInvoice{NrFact, Line, CodPr, Quantity,}
  • Customers {CodCl, Name, Adress, Phone}

I have tried this,but is not working

SELECT DISTINCT Name
FROM ( SELECT Name, COUNT(*) AS NrProd
       FROM Customers C
       INNER JOIN Invoice I ON C.CodCl=I.CodCl
       INNER JOIN LinesInvoice LI ON  I.NrFact=LI.NrFact
       WHERE CodPr IN
          (SELECT codpr
           FROM Customers C
           INNER JOIN c ON  C.CodCl=I.CodCl
           INNER JOIN  LinesInvoice LI ON I.Nrfact=LI.Nrfact
           WHERE UPPER(Name)= UPPER('Name 4'))
           GROUP BY Name) T1,
       (SELECT COUNT(CodPr) AS NrProd
        FROM Customers C
       INNER JOIN Invoice I ON C.CodCl=I.CodCl
       INNER JOIN  LinesInvoice LI ON  LI.NrFact=I.NrFact
       WHERE  UPPER(Name)= UPPER('Name 4')
       ) T2
  WHERE T1.NrProd=T2.NrProd;

Best Answer

I've created some samples for the answer as the tables and dummy data was not provided, correct me if they are wrong

---- CREATE TABLE STATEMENTS
CREATE TABLE Invoice 
(
NrFact int,
datefield date,
CodCI int
)
GO
CREATE TABLE LinesInvoice
(
NrFact int,
Line int,
CodPr varchar(30),
quantity int
)
GO
CREATE TABLE Customers
(
CodCI int,
Name varchar(100),
address varchar(100),
Phone varchar(35)
)
GO


---- INSERT 5 DUMMY CUSTOMERS
insert into Customers
VALUES(1, 'Name 1', 'aa','aa')
insert into Customers
VALUES(2, 'Name 2', 'bb','bb')
insert into Customers
VALUES(3, 'Name 3', 'cc','cc')
insert into Customers
VALUES(4, 'Name 4', 'dd','dd')
insert into Customers
VALUES(5, 'Name 5', 'ee','ee')



---Insert 5 dummy Invoices
insert into Invoice
values(1,getdate(),1)
GO
insert into Invoice
values(2,getdate(),2)
GO
insert into Invoice
values(3,getdate(),3)
GO
insert into Invoice
values(4,getdate(),4)
GO
insert into Invoice
values(5,getdate(),5)
GO


---Insert 10 dummy LinesInvoice

insert into LinesInvoice
values(1,1,'P1',84)
GO
insert into LinesInvoice
values(1,2,'P2',84)
GO
insert into LinesInvoice
values(2,1,'P3',84)
GO
insert into LinesInvoice
values(2,2,'P4',84)
GO
insert into LinesInvoice
values(3,1,'P3',84)
GO
insert into LinesInvoice
values(3,2,'P8',84)
GO
---- INVOICES OF CUSTOMER 4
insert into LinesInvoice
values(4,1,'P3',84)
GO
insert into LinesInvoice
values(4,2,'P385',84)
GO
---- END  INVOICES OF CUSTOMER 4
insert into LinesInvoice
values(5,1,'P4',84)
GO
insert into LinesInvoice
values(5,2,'P1',84)
GO

---- RESULT SHOULD SHOW ALL CUSTOMERS WHO HAVE BOUGHT PRODUCT P3 WHICH IS "NAME 2" & "NAME 3" 

After creating this dummy data & tables you can get the result you want by executing this query

select distinct [name]
from dbo.Customers c
inner join dbo.Invoice i on i.CodCI = c.CodCI
inner join dbo.LinesInvoice li on li.NrFact = i.NrFact
where li.CodPr in 
    (
    select distinct li.CodPr from
    dbo.Customers c 
    inner join dbo.Invoice i on i.CodCI = c.CodCI
    inner join dbo.LinesInvoice li on i.NrFact = li.NrFact
    where c.Name = 'Name 4'
    )
and c.Name <> 'Name 4';