Torsten Römer Torsten Römer - 1 month ago 6
SQL Question

Has SQL something like WHERE or HAVING ONLY?

I have a table like this:

| ID | SOMEKEY | STATUS |
-------------------------
| 1 | A | 0 |
| 2 | A | 1 |
| 3 | B | 1 |
| 4 | B | 1 |


Now I'd like to get the keys that have only STATUS 1, using JPA.

select o.somekey
from only_test o
where o.status = 1
and (select count(distinct s.status) from only_test s where s.somekey = o.somekey) = 1
group by o.somekey


Works also with JPA and gives only B which is what I want, but looks very clumsy to me.

Isn't there something like

having only status = 1


in SQL?

Answer

Use GROUP BY and HAVING:

select o.somekey
from only_test o
group by o.somekey
having max(o.status) = min(o.status) and min(o.status) = 1;

So, I guess the answer to your question is "yes", it does have something like WHERE ONLY, but it is phrased differently.