Mysql select values from table where not exist in other table

Question

I need an sql query to select a value from a table where it doesn't have a certain entry in another table. For example, considering the tables customers and customershop:

Customers

Id   Name
1    Steve
2    John
3    Bob

Customershop

CustomerId Item
1          Kiwi
1          Apple
2          Kiwi
2          Banana
3          Banana
3          Apple

I need a query for mysql so it can return Bob if looking for customers that don't have Kiwi in the customershop table. Any help is greatly appreciated.

For a start you could have used the title of your question as a search expression... — Shadow, Dec 25 '18 at 00:41

score 2 · Answer 1 · answered Dec 25 '18 at 00:29

As your title of your question already says, use NOT EXISTS and a correlated subquery.

SELECT *
       FROM customers c
       WHERE NOT EXISTS (SELECT *
                                FROM customershop s
                                WHERE s.customerid = c.id
                                      AND s.item = 'Kiwi');

Mysql select values from table where not exist in other table

1 Answers1