HQL Select in Join Statement

Question

I am trying to create HQL query that along with other details, also picks aggregate from other tables as well. Result expected is as follows:

member, collectionAmount, deductionAmount, saleAmount, prePaymentAmount
------------------------------------------------------------------------
<m>   ,    250          ,  550           ,   1500    ,    4550

The following query has been writed though

        "SELECT ctJoin.totalCollection, stJoin.saleAmount, dtJoin.deductionAmount, pJoin.prePaymentAmount, mp.amount, mp.memberId " +
        "FROM MemberPayment mp " +
        "LEFT JOIN ( " +
            "SELECT SUM(ct.price * ct.quantity) AS totalCollection , ct.member.memberId AS memberId  " +
            "FROM CollectionTransaction ct " +
            "WHERE ct.paymentPeriod.paymentPeriodId = ?1 AND ct.voided = false " +
            "GROUP BY ct.member.memberId) AS ctJoin ON mp.memberId.memberId = ctJoin.memberId " +
        "LEFT JOIN ( " +
            "SELECT SUM(st.saleAmount) AS saleAmount, st.memberId.memberId AS memberId " +
            "FROM SaleTransaction st " +
            "WHERE st.paymentPeriod = ?1 AND st.voided = false " +
            "GROUP BY st.memberId.memberId) AS stJoin ON stJoin.memberId = mp.memberId.memberId "+
        "LEFT JOIN ( " +
            "SELECT SUM(dt.amount) AS deductionAmount, dt.memberId.memberId AS memberId " +
            "FROM DeductionTransaction dt " +
            "WHERE dt.paymentPeriod = ?1 AND dt.voided = false " +
            "GROUP BY dt.memberId.memberId ) AS dtJoin ON dtJoin.memberId = mp.memberId.memberId "+
        "LEFT JOIN ( " +
            "SELECT SUM(p.paymentTransaction.amount) AS prePaymentAmount, p.memberId.memberId AS memberId " +
            "FROM MemberPayment p " +
            "WHERE p.paymentPeriodId = ?1 AND p.voided = false AND p.paymentTransaction.paymentType.id = 7 " +
            "GROUP BY p.memberId.memberId ) AS pJoin ON pJoin.memberId = mp.memberId.memberId "+
        "WHERE mp.memberId.memberId IN (?2)"

Intellij throws this error when run:

org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
... 69 common frames omitted
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 170 [SELECT ctJoin.totalCollection, stJoin.saleAmount, dtJoin.deductionAmount, pJoin.prePaymentAmount, mp.amount, mp.memberId FROM re.iprocu.model.MemberPayment mp LEFT JOIN ( SELECT SUM(ct.price * ct.quantity) AS totalCollection , ct.member.memberId AS memberId  FROM re.iprocu.model.CollectionTransaction ct WHERE ct.paymentPeriod.paymentPeriodId = ?1 AND ct.voided = false GROUP BY ct.member.memberId) AS ctJoin ON mp.memberId.memberId = ctJoin.memberId LEFT JOIN ( SELECT SUM(st.saleAmount) AS saleAmount, st.memberId.memberId AS memberId FROM re.iprocu.model.SaleTransaction st WHERE st.paymentPeriod = ?1 AND st.voided = false GROUP BY st.memberId.memberId) AS stJoin ON stJoin.memberId = mp.memberId.memberId LEFT JOIN ( SELECT SUM(dt.amount) AS deductionAmount, dt.memberId.memberId AS memberId FROM re.iprocu.model.DeductionTransaction dt WHERE dt.paymentPeriod = ?1 AND dt.voided = false GROUP BY dt.memberId.memberId ) AS dtJoin ON dtJoin.memberId = mp.memberId.memberId LEFT JOIN ( SELECT SUM(p.paymentTransaction.amount) AS prePaymentAmount, p.memberId.memberId AS memberId FROM re.iprocu.model.MemberPayment p WHERE p.paymentPeriodId = ?1 AND p.voided = false AND p.paymentTransaction.paymentType.id = 7 GROUP BY p.memberId.memberId ) AS pJoin ON pJoin.memberId = mp.memberId.memberId WHERE mp.memberId.memberId IN (?2)]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
... 76 common frames omitted

Question

How can expected result be actived by using either HQL, HPQL, QueryDSL or SpringDataJPA?

Update

MemberPayment model

public class MemberPayment extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "payment_id")
    private Integer paymentId;
    @JoinColumn(name = "member_id", referencedColumnName = "member_id")
    @ManyToOne(optional = false)
    private CoopMember memberId;

    @Column(name = "paymentperiod_id")
    private Integer paymentPeriodId;

    @JoinColumn(name = "payment_transaction")
    @OneToOne(cascade = CascadeType.ALL)
    private PaymentTransaction paymentTransaction;
}

CollectionTransaction, DeductionTransaction, SaleTransaction all have a

@JoinColumn(name = "member_id", referencedColumnName = "member_id")
@ManyToOne(optional = false)
private CoopMember memberId;

Answer 1

It's difficult to build SQL statement without seeing your model. But I've tried ))

select 
    m as member, 
    sum(ct.price * ct.quantity) as collectionAmount, 
    sum(dt.amount) as deductionAmount, 
    sum(st.saleAmount) as saleAmount, 
    sum(pt.amount) as prePaymentAmount
from 
    MemberPayment mp
    left join mp.collectionTransaction ct
    left join mp.saleTransaction st
    left join mp.deductionTransaction dt
    left join mp.paymentTransaction pt
    join pt.paymentType type
    join mp.memberId m
where
    ct.paymentPeriod = ?1 and ct.voided = false and 
    st.paymentPeriod = ?1 and st.voided = false and
    dt.paymentPeriod = ?1 and dt.voided = false and
    mp.paymentPeriod = ?1 and mp.voided = false and type.id = 7 and
    m.memberId in ?2
group by
    m

UPDATE

select 
    m as member, 
    sum(ct.price * ct.quantity) as collectionAmount, 
    sum(dt.amount) as deductionAmount, 
    sum(st.saleAmount) as saleAmount, 
    sum(pt.amount) as prePaymentAmount  
from 
    MemberPayment mp, CollectionTransaction ct, DeductionTransaction dt, SaleTransaction st
    join mp.memberId m
    join mp.paymentTransaction pt
where 
    mp.paymentPeriodId = ?1 and
    m.memberId in ?2 and 
    ct.memberId = m and ct.paymentPeriodId = mp.paymentPeriodId and
    dt.memberId = m and dt.paymentPeriodId = mp.paymentPeriodId and
    st.memberId = m and st.paymentPeriodId = mp.paymentPeriodId 
group by
    m