CODEWITHSUNDEEP
mysql
zzapper
zzapper

Reputation: 5041

MySQL Select Records if OTHER records don't match

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?

Upvotes: 0

Views: 1094

Answers (3)

user95144
user95144

Reputation: 1367

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

Upvotes: 1

ericdrum
ericdrum

Reputation: 61

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

MySQL CASE statement

Upvotes: 1

lc.
lc.

Reputation: 116538

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')

Upvotes: 4

Related Questions