CODEWITHSUNDEEP
sql
BORACHOF
BORACHOF

Reputation: 15

How Do I Filter My Results Based Off Of Other Entries?

I have written a statement that retrieves the person name, as well as the food they've eaten.

Person | Food Eaten
John   | Cake
Jack   | Ice Cream
Louis  | Hot Dog
John   | Pineapple Pizza

Now that I've retrieved what foods people have eaten, I would like to remove anyone who has eaten Pineapple Pizza from my list.

What type of clause would I want to use to create a unique list of people who haven't eaten Pineapple Pizza?

Upvotes: 0

Views: 35

Answers (2)

darcmadder
darcmadder

Reputation: 71

If you know the exact value of the food people have eaten, you can exclude it when you write your WHERE clause.

SELECT t.person, t.food_eaten
  FROM eaten_tbl t
 WHERE t.food_eaten <> 'Pineapple Pizza';

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use not exists :

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.person = t.person and t1.food = 'Pineapple Pizza'
                 );

Upvotes: 1

Related Questions