Web lists-archives.com

Re: Relational query question




It better to LEFT join rather then NOT IN




On Wed, Sep 30, 2015 at 6:00 PM, Mogens Melander <mogens@xxxxxxxxxxxxx>
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
>
>