Spring Spring - 1 year ago 67
SQL Question

SQL multiple where clause

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 (
select r
from T_REQUEST r
r.debit.id = d.id and
r.status = SUCCESSFUL

Please do ask if you need further information,

Answer Source

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:

  1. Use the entity names instead of tables names.
  2. If status is a field of type String be sure to enclose the statuses such as PENDING with single quotes.
  3. Follow syntax for the exists expression

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