subquery in join
java, jpa, querydsl, sql
Solution
Maybe like this
select c.id, c.name, a.id, a.street
from Company c
left join c.addresses a on a.addressStatusId = 1
and in Querydsl
query.from(c)
.leftJoin(c.addresses, a)
.on(a.addressStatusId.eq(1))
.list(c.id, c.name, a.id, a.street)
Problem
I need to select companies from the database with their active addresses (address.address_status_id = 1). If the address is inactive the address columns should contain nulls. The following query does exactly what I want: ``` select c.id, c.name, a.id, a.street from company c left join (select * from address where address_status_id = 1) a on c.id = a.company_id ``` I tried this in Java/QueryDSL: ``` JPAQuery query = new JPAQuery(entityManager); query = query.from(qCompany); query = query.leftJoin(company.addresses, new JPASubQuery().from(qAddress) .where(qAddress.addressStatusId.eq(AddressStatuss.ACTIVE.asBigDecimal())).list(qAddress)); ``` However JPA (and consequently the JPAQuery in QueryDSL) doesn't support a subquery in a JOIN clause Is there a way to rephrase the SQL statement so that it can be expressed in JPA/QueryDSL? edit: We're using Oracle DB and Hibernate JPA provider if it makes a difference.