SQL multiple where clause

Question

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
where 
r.debit.id = d.id and
r.status = SUCCESSFUL
)

Please do ask if you need further information,

Answer 1

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

Answer 2

This is not valid sql:

select d from T_DEBIT d 
where d.status=PENDING 
and where not exists (subquery)

The problem is that the word where appears twice in the main query. Simply remove it so your query becomes:

select d from T_DEBIT d 
where d.status=PENDING 
and not exists (subquery)

The other answers also make valid points.