CODEWITHSUNDEEP
sqlsql-serverdatabaserelational-database
Om Veer
Om Veer

Reputation: 55

SQL query to display names of customers who have purchased some particular products

These are the tables I have created and inserted the values accordingly:

CREATE TABLE Customer
(
     Customer_Id INTEGER IDENTITY (1,1) PRIMARY KEY,
     Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE Product
(
     Product_Id INTEGER IDENTITY (1,1) PRIMARY KEY,
     Product_Name VARCHAR(30)
)

CREATE TABLE Product_Purchase
(
     Purchase_Id INTEGER IDENTITY (1,1) PRIMARY KEY,
     Product_Id INTEGER NOT NULL,
     Customer_Id INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES ('Sandip'), ('Ankit'), ('Ashok'), ('Raj')

INSERT INTO Product (Product_Name)
VALUES ('Milk'), ('Egg'), ('Butter'), ('Paneer'), ('Curd')

INSERT INTO Product_Purchase (Product_Id, Customer_Id)
VALUES (4, 1), (1, 1), (3, 1)
       (4, 2), (1, 2), 
       (4, 3), (3, 3), (1, 3),
       (1, 4), (2, 4), (3, 4), (4, 4)

So I have 3 tables Customers, Product and Product_Purchase with values inserted.

Now I want the list of customers who purchased only (Milk, Butter, and Paneer).

All purchased items

Upvotes: 1

Views: 5542

Answers (2)

Mohammad
Mohammad

Reputation: 1577

List of customers who purchased exactly (Milk, Butter, and Paneer) :

select cu.Customer_Name
 from Customer as cu inner join dbo.Product_Purchase as pu
 on pu.Customer_Id =cu.Customer_Id 
 where cu.Customer_Id not in 
 (select distinct pu.Customer_id  from  dbo.Product as pr inner join dbo.Product_Purchase as pu on pr.Product_Id=pu.Product_Id where Product_Name not in ('Milk', 'Butter','Paneer'))
 group by  cu.Customer_Name
 having count(pu.Product_id)=3

Upvotes: 2

Ivo Breeden
Ivo Breeden

Reputation: 39

select customer_name
from Customer C, Product_Purchase PP, Product P
where C.Customer_Id = PP.Customer_Id
and   PP.Product_Id = P.Product_Id
and   Product_name in ('Milk', 'Butter', 'Paneer');

Upvotes: 0

Related Questions