It better to LEFT join rather then NOT IN
On Wed, Sep 30, 2015 at 6:00 PM, Mogens Melander <mog...@fumlersoft.dk> wrote: > Maybe not the most optimal, but (probably) the most simple: > > SELECT * FROM fruit > where id not in (select fruit from purchase > where customer=1); > > 1, 'Apples' > 3, 'Oranges' > > > On 2015-09-30 00:01, Richard Reina wrote: > >> If I have three simple tables: >> >> mysql> select * from customer; >> +----+--------+ >> | ID | NAME | >> +----+--------+ >> | 1 | Joey | >> | 2 | Mike | >> | 3 | Kellie | >> +----+--------+ >> 3 rows in set (0.00 sec) >> >> mysql> select * from fruit; >> +----+---------+ >> | ID | NAME | >> +----+---------+ >> | 1 | Apples | >> | 2 | Grapes | >> | 3 | Oranges | >> | 4 | Kiwis | >> +----+---------+ >> 4 rows in set (0.00 sec) >> >> mysql> select * from purchases; >> +----+---------+----------+ >> | ID | CUST_ID | FRUIT_ID | >> +----+---------+----------+---- >> | 2 | 3 | 2 | >> | 3 | 1 | 4 | >> | 4 | 1 | 2 | >> | 5 | 2 | 1 | >> +----+---------+----------+---- >> >> I am having trouble understanding a relational query. How can I select >> those fruits that Joey has not purchased? >> > > -- > Mogens > +66 8701 33224 > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
