Has SQL something like WHERE or HAVING ONLY?

Question

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 that also works with JPA (JPQL)?

Answer 1

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.