[SOLVED] Select Customers who purchased one specific Product

Select Customers who purchased one specific Product

We have two tables:

Customers:

Id	CustomerName
1	John
2	Ann
3	Catherine

Products:

Id	Customerld	ProductName	PurchaseDate
1	1	Milk	NULL
2	1	Bread	NULL
3	2	Milk	NULL
4	3	Bread	NULL

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?

Solution

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'
)