CODEWITHSUNDEEP
sqlsqlite
rakez25
rakez25

Reputation: 21

SQL Query Exclude Records

I want to query a database of guests that bought certain items. I want to see what customers bought item 'A' but not item 'B'.

I tried: 

SELECT customerName 
FROM Customers 
WHERE NOT item = 'A' AND item = 'B';

But I return customers that bought both items. I would like to exclude these customers from that query.

I am using SQLite

Upvotes: 1

Views: 154

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use EXISTS with NOT EXISTS: 

select c.*
from Customers c
where exists (select 1 
              from Customers c1 
              where c1.customerName = c.customerName and c1.item = 'A'
             ) and not exists 
             (select 1 
              from Customers c2 
              where c2.customerName = c.customerName and c2.item = 'B'
             );

Upvotes: 0

Pasantru
Pasantru

Reputation: 41

You can also use the MINUS operator which returns all rows in the first SELECT statement that are not returned by the second SELECT statement. Such as:

(SELECT customerName FROM Customers WHERE item='A')
MINUS
(SELECT customerName FROM Customers WHERE item='B');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

There are multiple ways to do this. I like to use group by and having, because it is very flexible for many conditions:

SELECT customerName
FROM Customers
GROUP BY customerName
HAVING SUM(CASE WHEN item = 'A' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN item = 'B' THEN 1 ELSE 0 END) = 0;

Upvotes: 1

Related Questions