Categories
database hibernate java jpql sql

conditional left outer join SQL query To JPQL

SQL FIDDLE LINK FOR THIS DATA

What is a jpql equivalent of following SQL query :

select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status="active") where App.status="active" and App.id=1;

Sample Data:

ij> select * from App;
ID |STATUS
----------------------
1 |active
2 |active1
3 |active3
5 |active
4 rows selected
ij> select * from App_Child;
ID |STATUS |D
----------------------------------
1 |active |1
2 |active11 |2
1 |active111 |3
1 |active |4
4 rows selected
ij> select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status="active") where App.status="active" and App.id=1;
ID |STATUS |ID |STATUS |D
---------------------------------------------------------
1 |active |1 |active |1
1 |active |1 |active |4
2 rows selected
ij> select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status="active") where App.status="active" and App.id=5;
ID |STATUS |ID |STATUS |D
---------------------------------------------------------
5 |active |NULL |NULL |NULL
1 row selected

EDIT : We are using jpa 2.0