There are two tables:

Customers:

1. ID
2. Name
3. Surname
4. City

Orders:

1. OrderId
2. CustomerId
3. Purchase
4. Price

I'm trying to find customer id,name,surname where he hasn't Purchase "Pizza". Any help to fix my query? I tried with cp.Purchase != "Pizza" but doesn't work

SELECT DISTINCT ID,FirstName,LastName
FROM Customers c 
INNER JOIN Orders cp ON c.ID = cp.OrderID 
ORDER BY c.ID
WHERE cp.Purchase LIKE '%Pizza%'

CodePudding user response：

try this

select * from Customers  where Id not in (
  select CustomerId From Orders WHERE cp.Purchase LIKE '%Pizza%'
)

CodePudding user response：

The right query is

SELECT DISTINCT c.ID, c.Name, c.Surname
FROM Customers c JOIN Orders o on c.ID = o.CustomerID
WHERE c.ID <> ALL (
SELECT c2.ID
FROM Customers c2 JOIN Orders o2 on c.ID = o2.CustomerID
WHERE o2.Purchase = 'Pizza')

This is because you are looking for who never bought a pizza, so you will select data of customers which ID never appear (<> ALL) in orders table in a record where a pizza was bought.

By the way, check also SQL base, try understand before getting the answer.