Solve Your Techie Problems at stackoverflow.com

I was told that http://stackoverflow.com was now preferred to Experts Exchange probably because it's totally free. I certainly got an answer to my MySQL problem in less than 15 minutes.

eg

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

The solution provided by lc was a sub select or multiple select query

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