MySQL Select Records if OTHER records don't match

Question

Hi I have a table with one record per person and per item of clothing

so

peter, jumper,blue
peter,trousers,green
sue,dress,green
peter,jumper,red
gordon,jumper,green

I want to write a query to select all people with green jumpers but only if they have no other color jumper

So in the above case it would ONLY select Gordon not greedy old Peter

is that possible?

Answer 1

This should work, but it's untested. The syntax might also be a little off as I still have Firebird SQL in my head, but you should get the general idea:

SELECT *
FROM myTable AS t1
WHERE t1.clothing = 'jumper' AND t1.color = 'green' 
AND NOT EXISTS(SELECT *
               FROM myTable AS t2
               WHERE t2.person = t1.person AND t2.clothing = 'jumper'
               AND t2.color <> 'green')

Answer 2

I have to run off to a meeting, but a CASE statement might help you out here.

MySQL CASE statement

Answer 3

SELECT *
FROM MyTable t1
WHERE Color = 'green'
AND NOT EXISTS (
    SELECT 1 
    FROM MyTable
    WHERE Color <> 'green'
    AND PersonName = t1.PersonName
)