Selecting rows except some others

Question

ID |   fruit |   who |  days_ago |
----------------------------------
1  |  orange |  Adam |         2 |
2  |  banana |  Adam |         3 |
3  |   apple |  Adam |         4 |
4  |    kiwi |  Adam |         2 |
6  |  banana | Jimmy |         3 |
7  |   apple | Jimmy |         5 |
8  |  grapes | Jimmy |         1 |
9  |  orange | Carol |         2 |
10 |  grapes | Carol |         6 |
11 |   lemon | Carol |         3 |

And my problem is: The table contained information about who bought what fruit and when (when is an extra information I just need to keep). I need to select all the fruits that Adam didn't buy.

ID |   fruit |   who |  days_ago |
----------------------------------
8  |  grapes | Jimmy |         1 |
10 |  grapes | Carol |         6 |
11 |   lemon | Carol |         3 |

And if Jimmy bought them, I don't want to know if Carol bought them too. And my result should be this:

ID |   fruit |   who |  days_ago |
----------------------------------
8  |  grapes | Jimmy |         1 |
11 |   lemon | Carol |         3 |

When I GROUP BY fruit, I lose information about who and days_ago (I don't understand how they're chosen). And when I select unique fruits and drop all that Adam bought, I lose grapes which both Jimmy and Carol bought.

This isn't the actual table I'm working on. Just a simplification of what I'm struggling with.

Any ideas?

Answer 1

If you want the fruits that Adam didn't buy, you can use a subquery for this. Have the subquery select the fruits he did buy, and then have the main query say "not those".

SELECT *
FROM fruits
WHERE fruit NOT IN(
    SELECT fruit
    FROM fruits
    WHERE who = "Adam"
)

DEMO: http://sqlfiddle.com/#!9/3974d/1

When you use GROUP BY, you are combining multiple rows into one. The other rows' data isn't gone, just hidden. Try using GROUP_CONCAT to see the list of who fields.

SELECT ID, fruit,
    GROUP_CONCAT(who) as who,
    GROUP_CONCAT(days_ago) AS days_ago
FROM fruits
WHERE fruit NOT IN(
    SELECT fruit
    FROM fruits
    WHERE who = "Adam"
)
GROUP BY fruit

DEMO: http://sqlfiddle.com/#!9/3974d/3