CODEWITHSUNDEEP
mysqlsql
Vector JX
Vector JX

Reputation: 179

Query where all the value is same for particular unique id

I have below mentioned database:

id  Value
1   A
1   B
1   B
2   A
2   B
3   B
3   B

I want to

  1. those id's only where all the value against those id is "B". (i.e id 3 in this case)
  2. using where id!=A while query but it give me all id's where both the value are present.

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You can use group by and having:

select id
from t
group by id
having min(value) = max(value) and min(value) = 'B';

If you have a separate table of ids, this might be faster:

select i.id
from ids i
where not exists (select 1 from idvalues iv where iv.id = i.id and iv.value <> 'B') and
      exists (select 1 from idvalues iv where iv.id = i.id and iv.value = 'B');  -- at least one B

This can take advantage of an index on idvalues(id, value).

Upvotes: 2

Related Questions