Here is my jOOQ query:
Table<Record> out = A.join(B)
.on(A.ID.eq(B.OID))
.and(A.WORD.eq(B.NEW_WORD)).asTable("out");
Table<Record> in = A.join(B)
.on(A.ID.eq(B.OID))
.and(A.WORD.eq(B.NEW_WORD)).asTable("in");
Condition inCondition = in.getField(A.ADJECTIVE).eq(adj)
.and(in.getField(A.WORD).in(listofwords))
.and(in.getField(A.VERB).notIn(listofverbs));
.and(in.getField(B.ID).lessOrEqual(id));
Table<Record> nested = factory.select(
out.getField(A.ID),
out.getField(A.WORD),
out.getField(A.VERB),
out.getField(B.ID),
out.getField(B.TIME))
.from(out)
.where(out.getField(B.ID)
.eq(factory.select(max(in.getField(B.ID)))
.from(in)
.where(inCondition)
.and(out.getField(A.VERB)
.eq(in.getField(A.VERB)))
.groupBy(in.getField(A.VERB)))
).asTable("nested");
Result<Record> records = factory.select(nested.getFields())
.from(nested)
.orderBy(nested.getField(B.ID).desc())
I did the semi join referencing:
https://groups.google.com/forum/#!topic/jooq-user/S3uTIMGgQ0w
I want the rows of the join of A and B corresponding to the max B.ID for
each A.VERB. I did the semi join referencing:
https://groups.google.com/forum/#!topic/jooq-user/S3uTIMGgQ0w
I believe the error comes from doing out.getField(A.ID) and
in.getField(B.ID) which both get aliased as nested.id
I'm not sure how I can alias one of the ID fields, eg. B.ID so that I can
reference them separately in the select for the nested table. A.ID and B.ID
do not have any relationships together
On Friday, August 30, 2013 7:12:39 AM UTC-7, Lukas Eder wrote:
>
> Hello,
>
> I'm not quite sure what you're trying to do. Nesting joins I suppose? Can
> you express the SQL statement that you're trying to create, and we'll see
> how it can be translated to the jOOQ API
>
> Cheers
> Lukas
>
>
> 2013/8/30 <[email protected] <javascript:>>
>
>> I have 2 Tables both the same column name, id. However, these column
>> names do not have any foreign key relationship.
>>
>> Table A:
>> id,
>> other,
>> stuff
>>
>> Table B:
>> id,
>> more,
>> things
>>
>> I use the join of both these tables and alias it so I can do a semi join.
>> A.join(B).asTable("inner");
>> A.join(B).asTable("outer");
>>
>> I would like to use both id columns, however when I use inner.getField(
>> A.ID) and inner.getField(B.ID), they both go to same aliased field
>> inner.id
>>
>> How can I change the B.ID column of the joined table to have a different
>> alias?
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "jOOQ User Group" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
