Joining to a subselect

[Simon]

Hi All,

I am playing around with QueryDSL to see if it will be useful for my current project (which is about creating SQL dynamically at runtime)

I have been looking at http://www.querydsl.com/static/querydsl/4.0.9/reference/html/ch02s03.html  (Querying SQL) however it is not clear to me how to go about creating a join to a (sub)select.

I can see how to use subselects in the select statement, and in the where clause, but can't see how to achieve something like this

select t1.a,t1.b,t1.c,SubSel1.d,SubSel1.e
from table1 as t1
join (select d,e,f
      from table2) as SubSel1
on t1.a = SubSel1.d

It this even possible ?

with thanks

Simon

[Jared Boone]

Hi Simon,

I believe it's not possible. Timo answered a similar question on stackoverflow.com here: http://stackoverflow.com/questions/6942108/jpql-querydsl-join-subquery-and-get-aliased-column.

Hope this helps,

Jared

[Simon]

Thanks Jared,

I did see that stackoverflow question, I am however not using JPQL - just pure SQL - and so was hoping that the JPA limitation would not apply here.

[timowest]

This query

select t1.a,t1.b,t1.c,SubSel1.d,SubSel1.e
from table1 as t1
join (select d,e,f
      from table2) as SubSel1 
on t1.a = SubSel1.d

can be expressed using

q.select(t1.a, t1.b, t1.c, sub.d, sub.e)

 .from(t1)

 .join(SQLExpressions.select(t2.d, t2.e, t2.f).from(t2), t2)

 .on(t1.a.eq(t2.d))

 .fetch();

[Simon]

Timo,
I was hoping for something like that.  I had not discovered SQLExpressions.
This is good news.  Thanks very much.

On Thursday, 7 April 2016 20:27:11 UTC+12, Simon wrote: