I couldn't find if using multiple where clauses like this is valid or not(I use JPA, MySQL) I need multiple where clauses one of them will be a "not" here, or am I missing something?
select d from T_DEBIT d where d.status=PENDING and
where not exists (
from T_REQUEST r
r.debit.id = d.id and
r.status = SUCCESSFUL
JPA provides support for subqueries. See the specification
Subqueries may be used in the WHERE or HAVING clause. The syntax for subqueries is as follows:
subquery ::= simple_select_clause subquery_from_clause [where_clause [groupby_clause] [having_clause]
Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of the specification.
Your query appears to be written in SQL, converting it to JPQL will require a few things:
statusis a field of type
Stringbe sure to enclose the statuses such as
PENDINGwith single quotes.
I believe you could also write your query as a join (PSEUDO CODE):
select d from T_DEBIT d left join T_REQUEST tr on d.id = tr.debit_id where d.status = 'PENDING' and tr.status = 'SUCCESSFUL` and tr.debit_id is null