SQL Only return value if condition is met with duplicate info

Question

I want to write a SQL query that only returns "Item" from the database only if a strict condition is met. Effectively I want to say "Get Item only if MyValue is never between 6 and 9.

 Item | MyValue
   1  |  3
   1  |  6
   1  |  9
   1  |  12
   2  |  1
   2  |  4
   3  |  3
   3  |  6
   3  |  9

I've come up with:

  SELECT * FROM data WHERE(MyValue NOT BETWEEN 6 and 9)

but that gets me this:

   1  |  3
   1  |  12
   2  |  1
   2  |  4
   3  |  3

Where as the goal is to simply obtain this:

 Item |
   2  |

I hope this makes sense. Any help would be appreciated.

Answer 1

Try this instead:

SELECT DISTINCT Item 
FROM data
WHERE item NOT IN(SELECT Item 
                  FROM data 
                  WHERE Myvalue BETWEEN 6 AND 9);

• SQL Fiddle Demo

Answer 2

You can do this with aggregation and a having clause:

select item
from t
group by item
having sum(case when MyValue BETWEEN 6 and 9 then 1 else 0 end) = 0;

The having clause counts the number of rows that have values between 6 and 9. It passes when no rows have this condition.