Query in JPA2 with LEFT JOIN on INNER JOIN

How write a query in JPA2 with LEFT JOIN on INNER JOIN?

select a.description, a.id, p1.description, p2.description,p3.description
from action a
left join action_profile ap1 on ap1.id_action=a.id
inner join profile p1 on ap1.id_profile=p1.id and p1.name='?'
left join action_profile ap2 on ap2.id_action=a.id
inner join profile p2 on ap2.id_profile=p2.id and p2.name='?'
where a.id =?;

Thanks

The syntax of using JOIN in explained on this manual page.

Using ON in a JOIN close is invalid in JPQL. Instead of ON you may  include all the variables in the FROM clause:

FROM action a, action_profile ap1, profile p1, ...

and add the constraints in the WHERE clause.

But actually your query may indicate that you model is not optimized for JPA. Instead of connecting objects using ids (which is more appropriate in RDBMS and SQL), use should consider using direct references (relationships) between entity objects, and then you would be able to use ordinary JOIN of JPQL.

Hi, this is my critera query:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
   CriteriaQuery<ActionProfileAdminAdvancedResponse_> cq = cb.createQuery(ActionProfileAdminAdvancedResponse_.class);
   CriteriaQuery<BigDecimal> profileCriteriaQuery = cb.createQuery(BigDecimal.class);
   CriteriaQuery<String> actionDescriptionCriteriaQuery = cb.createQuery(String.class);

   Root<Action> root = cq.from(Action.class);
   Selection[] selections = new Selection[profiles.length+1];

   Selection<String> actionDescriptionSelection = actionDescriptionCriteriaQuery.select(root.get(Action_.description)).getSelection();
   selections[0]=actionDescriptionSelection;
  
   Predicate masterPredicate = cb.conjunction();
   Predicate predicateOnPath = cb.equal(root.get(Action_.path).get(Path_.id), idPAth);
   masterPredicate = cb.and(masterPredicate,predicateOnPath);
   for (int i = 0; i < profiles.length; i++) {
    Join <Action,ActionProfile> actionJoinActionProfile = root.join(Action_.actionProfiles,JoinType.LEFT);
    Selection<BigDecimal> selection = profileCriteriaQuery.select(actionJoinActionProfile.get(ActionProfile_.profile).get(Profile_.id)).getSelection();
   
//      left join action_profile ap1 on ap1.id_action=a.id and ap1.id_profile=1 
     
    selections[i+1]=selection;
    Predicate predicateOnProfile = cb.equal(actionJoinActionProfile.get(ActionProfile_.id), profiles[i]);
    masterPredicate = cb.and(masterPredicate,predicateOnProfile);
   }
  
   cq.select(cb.construct(ActionProfileAdminAdvancedResponse_.class, selections));
   cq.where(masterPredicate);

   TypedQuery<ActionProfileAdminAdvancedResponse_> query = entityManager.createQuery(cq);

   return query.getResultList();

This is the query automatically generated by Hibernate when I run this piece of code.

select

          action0_.DESCRIPTION as col_0_0_,
          actionprof1_.ID_PROFILE as col_1_0_,
          actionprof2_.ID_PROFILE as col_2_0_,
          actionprof3_.ID_PROFILE as col_3_0_,
          actionprof4_.ID_PROFILE as col_4_0_,
          actionprof5_.ID_PROFILE as col_5_0_,
          actionprof6_.ID_PROFILE as col_6_0_,
          actionprof7_.ID_PROFILE as col_7_0_,
          actionprof8_.ID_PROFILE as col_8_0_,
          actionprof9_.ID_PROFILE as col_9_0_
      from
          ACTION action0_
      left outer join  ACTION_PROFILE actionprof1_    on action0_.ID=actionprof1_.ID_ACTION
      left outer join  ACTION_PROFILE actionprof2_    on action0_.ID=actionprof2_.ID_ACTION
      left outer join  ACTION_PROFILE actionprof3_    on action0_.ID=actionprof3_.ID_ACTION
      left outer join  ACTION_PROFILE actionprof4_    on action0_.ID=actionprof4_.ID_ACTION
      left outer join  ACTION_PROFILE actionprof5_    on action0_.ID=actionprof5_.ID_ACTION
      left outer join  ACTION_PROFILE actionprof6_    on action0_.ID=actionprof6_.ID_ACTION
      left outer join  ACTION_PROFILE actionprof7_    on action0_.ID=actionprof7_.ID_ACTION
     left outer join   ACTION_PROFILE actionprof8_    on action0_.ID=actionprof8_.ID_ACTION
     left outer join   ACTION_PROFILE actionprof9_    on action0_.ID=actionprof9_.ID_ACTION
     where
         1=1
         and action0_.ID_PATH=14
         and actionprof1_.ID=11
         and actionprof2_.ID=10
         and actionprof3_.ID=1
         and actionprof4_.ID=2
         and actionprof5_.ID=3
         and actionprof6_.ID=4
         and actionprof7_.ID=6
         and actionprof8_.ID=7
         and actionprof9_.ID=9

while my query is

select distinct (a.description), a.id, ap1.id_profile, ap2.id_profile, ap3.id_profile, ap4.id_profile, ap5.id_profile,ap6.id_profile,ap7.id_profile,
ap8.id_profile, ap9.id_profile

from action a
inner join path p on p.id = a.id_path and p.id= 4
left join action_profile ap1 on ap1.id_action=a.id and ap1.id_profile=1
left join action_profile ap2 on ap2.id_action=a.id and ap2.id_profile=2
left join action_profile ap4 on ap4.id_action=a.id and ap4.id_profile=3
left join action_profile ap3 on ap3.id_action=a.id and ap3.id_profile=4
left join action_profile ap5 on ap5.id_action=a.id and ap5.id_profile=5
left join action_profile ap6 on ap6.id_action=a.id and ap6.id_profile=6
left join action_profile ap7 on ap7.id_action=a.id and ap7.id_profile=7
left join action_profile ap8 on ap8.id_action=a.id and ap8.id_profile=8
left join action_profile ap9 on ap9.id_action=a.id and ap9.id_profile=9;

How to specify multiple conditions on left join ?

You cannot specify multiple conditions on left join in JPQL.

The syntax of JPQL is different than the syntax of SQL.

You may, however, specify the additional conditions in the query WHERE clause.

But the result of the query is different if insert the WHERE conditions instead of in the left join conditions.

"LEFT JOIN action_profile ap1 ON ap1.id_action=a.id AND ap1.id_profile=1"

This is different than writing

WHERE ap1.id_profile=1

JPQL is similar to SQL but not identical, and it may not have this specific SQL ability that you need.

Sorry,

but I use Criteria API.