MySQL Subquery or Multiple Select Queries
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
A solution provided is 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')
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
A solution provided is 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')
Labels: multi-query, mysql, stackoverflow, subquery

0 Comments:
Post a Comment
<< Home