CODEWITHSUNDEEP
mysqlselect
discostu
discostu

Reputation: 291

How can I select a row with exclusively one value?

This is how my table looks:

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

I've just need the rows which has exclusively color= "Red".
It must return "a" ("b" contains value "Blue" too).

How can I set the select?

Upvotes: 4

Views: 101

Answers (2)

Taryn
Taryn

Reputation: 247630

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 the subquery you can use:

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

See SQL Fiddle with Demo

Upvotes: 3

Meherzad
Meherzad

Reputation: 8553

TRY his query

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

Upvotes: 2

Related Questions