CODEWITHSUNDEEP
sqlgroup-bycounthaving-clause
Chris
Chris

Reputation: 311

SQL - Return UserIds that only reviewed an item as "poor"

What is the best way to query the below problem in SQL?

I want to return the UserID that only reviewed items as "Poor". In the table below, only UserID 1 should be returned.

UserID   ItemID    Rank
-------|---------|----------
1      | 1       | Poor
2      | 1       | Fair
1      | 2       | Poor
2      | 2       | Excellent
2      | 3       | Poor

The query (based off of the table above) should return only the ID 1, as that ID never reviewed any other items as anything other than "Poor"

UserID
-------
1

Upvotes: 1

Views: 24

Answers (2)

GMB
GMB

Reputation: 222462

You could aggregate and filter with a conditional sum in the having clause:

select userid
from mytable
group by userid
having sum(case when rank != 'Poor' then 1 else 0 end) = 0

If you are running MySQL, this gets even shorter:

select userid
from mytable
group by userid
having sum(rank != 'Poor') = 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use group by and having:

select userid
from t
group by userid
having min(rank) = max(rank) and
       min(rank) = 'Poor';

Upvotes: 3

Related Questions