CODEWITHSUNDEEP
sqlsql-servert-sql
Zwick44
Zwick44

Reputation: 57

T-SQL Returning records (customers) where value not present

Ok, this seems like it should be an easy one so I will try to simplify what I am trying to do.

Say I have this select statement:

SELECT a.customer, b.fruit
FROM Customer a 
INNER JOIN Order b ON a.customer = b.Customer

Which would return the following:

Customer         Fruit
-------------------------
Jane             Apple
Jane             Banana
Bob              Apple
Bob              Orange
Bob              Grape
John             Apple
John             Banana
Ann              Tangerine
Ann              Orange
Ann              Banana

What I would like to pull from this result set is a list of customers who have never ordered, say, a 'Tangerine', resulting in the following list:

     Customer
     --------
      Jane
      Bob
      John

How would one go about do this? Thanks in advance!

Upvotes: 1

Views: 68

Answers (3)

paparazzo
paparazzo

Reputation: 45096

Existing answers are missing distinct customers

SELECT distinct c.customer
  FROM Customer c
 WHERE NOT EXISTS ( SELECT 1 
                      FROM Customer T
                     WHERE T.customer = c.customer
                       And T.Fruit = 'Tangerine'
                  );

And I don't like tables and column the same
That would be more and Order table

Upvotes: 0

Lamak
Lamak

Reputation: 70658

There are many ways to do this:

SELECT *
FROM Customer c
WHERE NOT EXISTS(SELECT 1 FROM Order
                 WHERE customer = c.customer
                 And Fruit = 'Tangerine');

Using NOT IN:

SELECT *
FROM Customer c
WHERE c.Customer NOT IN (SELECT Customer FROM Order
                         WHERE Customer IS NOT NULL
                         And Fruit = 'Tangerine');

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Here is one method using left join:

SELECT c.customer
FROM Customer c LEFT JOIN
     Order o
     ON c.customer = o.Customer AND o.fruit = 'Tangerine'
WHERE o.Customer IS NULL;

Upvotes: 1

Related Questions