CODEWITHSUNDEEP
sqlsql-server
Elijah
Elijah

Reputation: 197

Select Customers who purchased one specific Product

We have two tables:

  1. Customers:

enter image description here

  1. Products:

enter image description here

The goal is to select [Id] and [CustomerName] of Customers who purchased Milk AND did not purchase Bread. In the case the correct query should return a customer with Id 2 (Ann).

The query which I thought of (and which is obviously incorrect) is:

select CustomerName from dbo.Customers
where Id in 
(
  select CustomerId from dbo.Products
  where ProductName = 'Milk' and ProductName != 'Bread'
)

It returns two customers: 1 (John) and 2 (Ann). How to rewrite the query so it would return only customer with Id 2?

Upvotes: 0

Views: 6160

Answers (5)

Bart Hofland
Bart Hofland

Reputation: 3905

If normalization is not the case, efficiency isn't either. Here you go:

select CustomerName from dbo.Customers
where
   Id in (select CustomerId from dbo.Products where ProductName = 'Milk')
   and Id not in (select CustomerId from dbo.Products where ProductName = 'Bread')

Ah. Just saw it could be considered a duplicate of Eric's answer. Using exists may be a little faster indeed.

Upvotes: 0

Oussema Miled
Oussema Miled

Reputation: 618

SELECT P.Id ,C.Customers 
FROM Customers AS C , Product AS P
WHERE (C.Id = P.CustomerId)
AND (P.ProductName = 'Milk') 
AND NOT EXISTS (
    SELECT 1
    FROM Products
    WHERE CustomerId = C.Id
        AND ProductName = 'Bread'
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I am inclined to use aggregation for this. Here is one method:

select c.customerId
from dbo.Products p
where p.productName in ('Milk', 'Bread')
group by c.customerId
having sum(case when p.productName = 'Milk' then 1 else 0 end) > 0 and
       sum(case when p.productName = 'Bread' then 1 else 0 end) = 0 ;

You can add the join in to get the customer name, if you really need that.

Basically, this counts the number of rows for each customer that have 'Milk'. The > 0 says there is at least one. It then counts the number of rows that have 'Bread'. The = 0 says that there are none.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You don't need to use two exists, just use where clause with not exists :

select c.*
from customer c
where ProductName = 'Milk' and
      not exists (select 1 from Products p where p.CustomerId = c.id and p.ProductName = 'Bread');

Upvotes: 1

Eric
Eric

Reputation: 3257

You can try the query below

SELECT CustomerName 
FROM dbo.Customers c
WHERE EXISTS (
    SELECT 1
    FROM dbo.Products
    WHERE CustomerId = c.Id
        AND ProductName = 'Milk'
) AND NOT EXISTS (
    SELECT 1
    FROM dbo.Products
    WHERE CustomerId = c.Id
        AND ProductName = 'Bread'
)

Upvotes: 3

Related Questions