Menu
I need a JPQL for the MySQL query:
SELECT *
FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.table1.id
INNER JOIN (SELECT * FROM table1 t3
INNER JOIN table2 t4 ON t3.id = t4.table1.id
WHERE t3.name = 'xxx') subTable
ON t1.number = subTable.number
WHERE t1.number = '5'
AND id = '3'
610
JPQL provides an additional type of identification variable, a join variable, which represent a more limited iteration over specified collections of objects. In JPQL, JOIN can only appear in a FROM clause. The INNER keyword is optional (i.e. INNER JOIN is equivalent to JOIN).
Inner Join will execute sub query only once.
A subselect is a query embedded into another query. It's a powerful feature you probably know from SQL. Unfortunately, JPQL supports it only in the WHERE clause and not in the SELECT or FROM clause. Subqueries can return one or multiple records and can use the aliases defined in the outer query.
A subquery can be used with JOIN operation. In the example below, the subquery actually returns a temporary table which is handled by database server in memory. The temporary table from the subquery is given an alias so that we can refer to it in the outer select statement.
Your query seems quite pathological, perhaps say what result you are trying to query, and include your object model.
In general, JPQL does not support sub-selects in the from clause, so your query is not directly convertable to JPQL.
You can always just execute it as a JPA native SQL query, since you seem to be comfortable with SQL than JPQL.
137
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
