Aliasing a column name in two joined tables with same column name

[ky...@kylehsu.com]

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?

[Lukas Eder]

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 <ky...@kylehsu.com>

--
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.

[kyl...@squareup.com]

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

[Lukas Eder]

Hello Kyle,

I'm sorry about the delay, and thanks for the detailed message.

2013/8/30 <kyl...@squareup.com>

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

[Kyle]

Question:

I am using MySQL as a backend so how would:

-- MySQL, H2, and others

SELECT t.a, t.b

FROM (

  SELECT 1 a, 2 b

) t

translate to jooq? I have tried multiple approaches yet I always end up with org.jooq.exception.DataAccessException which complains about the syntax of the aliased table. (I am using MySQL)

Also, you suggest to inline the in and out tables, however in the semi-join condition:

.and(out.getField(A.VERB)

                    .eq(in.getField(A.VERB)))

                .groupBy(in.getField(A.VERB)))

how would I express the condition that out.A.VERB equals in.A.VERB 

Thanks!

[Lukas Eder]

2013/9/7 Kyle <ky...@kylehsu.com>

Question:

I am using MySQL as a backend so how would:

-- MySQL, H2, and others

SELECT t.a, t.b

FROM (

  SELECT 1 a, 2 b

) t

translate to jooq? I have tried multiple approaches yet I always end up with org.jooq.exception.DataAccessException which complains about the syntax of the aliased table. (I am using MySQL)

Can you list a couple of example attempts that failed? This should work:

Table<?> t = table(

    select(val(1).as("a"), val(2).as("b"))

).as("t");

DSL.using(configuration)

   .select(t.field("a"), t.field("b"))

   .from(t);

 

Also, you suggest to inline the in and out tables, however in the semi-join condition:

.and(out.getField(A.VERB)

                    .eq(in.getField(A.VERB)))

                .groupBy(in.getField(A.VERB)))

how would I express the condition that out.A.VERB equals in.A.VERB 

Once those join expressions are inlined, you don't need to dereference A.VERB from "in" or "out", but directly from A instead. Wouldn't this simplify things? In other words, I still don't quite understand why the joined table is wrapped and aliased in a separate table reference before passing it to the from clause. This abstraction step seems unnecessary to me, while being the major source of column ambiguity, even in SQL. Although I have to admit, I'm a bit lost in this query.