Web lists-archives.com

Re: Relational query question




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