CODEWITHSUNDEEP
mysql
David Šili
David Šili

Reputation: 117

Making opposite of select in MySQL query

I have been struggling with getting an opposite search in MySQL. Here is a sample database:

+-------+--------+  
| name  | fruits |  
+-------+--------+  
| Jimmy | pear   |  
| Jimmy | apple  |  
| Jimmy | peach  |  
| Becky | pear   |  
| Garry | apple  |  
| Garry | pear   |  
+-------+--------+

What I'm trying to get is names of all people that did not eat apple. When I do query searching for WHERE fruits NOT LIKE "%apple%" I get not only Becky (which is the only one who did not eat it), but also two fields of Jimmy and one of Garry, because there were other occurrences where they ate something else.
How should I write a query?

Upvotes: 1

Views: 917

Answers (1)

Jason
Jason

Reputation: 89102

this is one possible approach

select name where name not in (select name where fruits like '%apple%')

Upvotes: 4

Related Questions