How to select a row with a single value?

Question

This is what my table looks like:

╔══════╦═══════╗ ║ USER ║ COLOR ║ ╠══════╬═══════╣ ║ a ║ Red ║ ║ b ║ Blue ║ ║ c ║ Blue ║ ║ b ║ Red ║ ║ a ║ Red ║ ║ c ║ White ║ ╚══════╩═══════╝ 

I need strings that have only color = "red".
It should return "a" ("b" also contains the value "Blue").

How to set a choice?

Answer 1

You can use:

 select * from yourtable t1 where color = 'red' and exists (select user from yourtable t2 where t1.user = t2.user group by user having count(distinct color) = 1) 

See SQL Fiddle with Demo

Or without a subquery that you can use:

 select * from yourtable group by user HAVING SUM(color = 'red') = COUNT(*) 

See SQL Fiddle with Demo

Answer 2

CHECK his request

 Select * from tbl where color = 'RED' AND USER not in (Select USER from tbl where color <> 'RED');