Hello Kyle, I'm sorry about the delay, and thanks for the detailed message.
2013/8/30 <[email protected]> > 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 > Yes, your analysis is correct. There is a lot of ambiguity in your SQL statement with respect to ID columns. As a matter of fact, I doubt that your statement would be non-ambiguous in SQL itself. "out", for instance contains two references called "out.ID", which create an ambiguity between them. This is not resolved when you rename the SELECT to "nested", which contains two references named "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 > While I can see your intent with nesting joined tables, I think this will also be tricky to solve in SQL. When renaming (A join B on ...) AS out You should really be renaming the joined table using derived column lists (A join B on ...) AS out(A_ID, A_VERB, B_ID, B_XXX) jOOQ supports derived column lists and simulates them where they aren't natively supported in SQL. This is explained here: http://blog.jooq.org/2013/01/07/simulating-the-sql-standard/ In the mean time, I can offer you these workarounds: - Create a database view to express the reusable relation A join B on ... This view will be available from jOOQ's code generator output - Inline "in" and "out" instead of creating nested joined tables. While being a bit more verbose at programming time, it is certainly more readable later on. - Rename ID columns in your SQL statement, to resolve ambiguities. I hope this helps. Please, let me know if / how you resolved this issue. I'm always curious about how people deal with complex SQL and jOOQ. Cheers Lukas > > 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]> >> >>> 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 jooq-user+...@**googlegroups.com. >>> >>> For more options, visit >>> https://groups.google.com/**groups/opt_out<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. > -- 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.
