SQL Simple Query - Multiple entries filtering

Question

Newbie SQL Query question: I'm using Oracle SQL Developer.

I have a table:

<name>  <color>
steve    red
mark     red
steve    green
john     red
ryan     red

What I would like to do is have the results only display the names that contain RED in the color column.
The results would be:

     mark
     john
     ryan

Since steve has a color green as well, it would not show up.

Anyone out there knows?

EDIT:
THANK YOU FOR THE ANSWERS. I do see that I should have said "red and no other color" in the results. Sorry for the confusion but thank you for the super speedy replies!

Answer 1

You will just query with a WHERE clause and a NOT IN.

SELECT name
FROM yourTable
WHERE color = 'red'
   AND name NOT IN (
                    SELECT name
                    FROM yourTable
                    WHERE color <> 'red'
                )

see SQL Fiddle with Demo

Or you can use WHERE with NOT EXISTS:

SELECT name
FROM yourTable t1
WHERE color = 'red'
   AND NOT EXISTS (
                    SELECT  t2.name
                    FROM yourTable t2
                    WHERE  t2.color <> 'red'
                     AND t1.name = t2.name
                )

see SQL Fiddle with Demo

Answer 2

Just for the fun of it, here is a solution using windowing functions:

select *
from (
  select name, 
         color, 
         count(distinct color) over (partition by name) as color_cnt
  from the_table
) t
where color = 'red' 
and color_cnt = 1;

SQLFiddle: http://sqlfiddle.com/#!4/d267e/3

There is a slight change that this might be faster than the solutions with a sub-select because most probably only a single scan over the table will be needed (although possibly the other solutions could use an index on the color column which could make the two scans cheaper than the single scan)