On 9/29/2015 1:27 PM, Ron Piggott wrote:
On 29/09/15 13: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?
I think you are going to want to use a "LEFT JOIN" using "purchases" as
the common table to join with a WHERE purchases.FRUIT_ID IS NULL
SELECT f.name
FROM fruit f
LEFT JOIN purchases p
on f.id = p.fruit_id
INNER JOIN customer c
on p.cust_id = c.id
and c.name='Joey'
WHERE c.id IS NULL;
You have to make that "and...Joey" part of the LEFT JOIN to be selective
for just "what Joey bought". it is the WHERE c.id IS NULL part that
filters out and returns only the stuff that Joey did not buy.
If you put the c.name='Joey' term in the WHERE clause then you force a
value to exist at that point of the query turning your LEFT JOIN into
INNER JOIN (which would only show you what Joey did buy).
If you put WHERE c.name !='Joey' into the WHERE clause, then you would
get the list of fruits that anyone else but Joey had purchased.
To see how this works and to understand the process a little better,
expose all 3 layers of the problem as a big matrix (you'll get all 48
row combinations).
SELECT f.id as f_id, f.name, p.id as p_id, p.cust_id, p.fruit_id, c.id
as c_id, c.name
FROM fruit f
LEFT JOIN purchases p
on f.id = p.fruit_id
LEFT JOIN customer c
on p.cust_id = c.id
From here, look at when the columns are NULL and when they aren't. Then
experiment with different conditions. You are almost there. This should
push you right to the top of the learning curve.
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
