Hi Stefan,
Can you try doing it the way the manual page suggests it?
https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/aliased-tables/aliased-generated-tables/
I.e.
var pl1 = SecuritydbUser.SECURITYDB_USER.as("pl1");
var pl2 = SecuritydbUser.SECURITYDB_USER.as("pl2");
And now work with pl1 and pl2
On Wed, Sep 20, 2023 at 9:41 AM Stefan Sator <sator.stefa...@gmail.com>
wrote:
> Hello Lukas,
>
> thanks for your reply.
>
> I took the sql analogy a little bit to far and tried something like this:
> .join(pl.as("x")).on(p.FK_PROJECT_LEADER.eq(pl.ID))
> This will not do anything useful. Is there maybe a way to prevent this
> "error"?
>
> Then I got stuck because I did not know how to say this:
> .join(pl.as("x")).on(p.FK_PROJECT_LEADER.eq("x".ID))
>
>
> I did not find the documentation you linked to because I was looking for
> something like "alias join / alias joined tables".
>
> I found this
> https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/aliased-tables/aliased-join-tables/
> but that was not what I was looking for.
>
> I also did search in your book.
>
> Then I god even more confusing by the fact that one can do what is
> described in
>
> https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/aliased-tables/aliased-generated-tables/
> without even using the .as("a").
>
> Just like this:
> var p = ProjectdbProject.PROJECTDB_PROJECT;
>
> var fetch = dbcontext.select(
> p.UUID,
> p.NAME,
> p.NUMBER,
> p.PROJECT_STATUS,
> p.EMPLOYER,
> pl.FORENAME,
> pl.SURNAME,
> d.FORENAME,
> d.SURNAME,
> p.CHANGED_AT)
> .from(p)
> .join(pl.as(pl1)).on(p.FK_PROJECT_LEADER.eq(pl.ID))
> .join(pl.as(d1)).on(p.FK_DEPUTY.eq(pl.ID))
> .where(condition)
> .orderBy(orderFields)
> .offset(offset)
> .limit(limit)
> .fetch(mapping((r1, r2, r3, r4, r5, r6, r7, r8, r9, r10) ->
> new ProjectTableUIModel(r1, r2, r3, r4, r5, r6, r7, r8, r9, r10)));
>
>
>
> On Tuesday, 19 September 2023 at 17:19:03 UTC+2 lukas...@gmail.com wrote:
>
>> Hi Stefan,
>>
>> Just alias your tables, like in SQL:
>>
>> https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/aliased-tables/aliased-generated-tables/
>>
>> I hope this helps,
>> Lukas
>>
>> On Tue, Sep 19, 2023 at 5:00 PM Stefan Sator <sator.s...@gmail.com>
>> wrote:
>>
>>> Hello everyone,
>>>
>>> I want to join twice between a project table and a user table.
>>>
>>> Users can be attached to a project in the role as project leader or
>>> deputy.
>>>
>>> So far I tried(and different versions):
>>> var p = ProjectdbProject.PROJECTDB_PROJECT;
>>> var pl = SecuritydbUser.SECURITYDB_USER;
>>>
>>> int i = dbcontext.fetchCount(DSL.selectDistinct(p.ID)
>>> .from(p)
>>> .join(pl).on(p.FK_PROJECT_LEADER.eq(pl.ID))
>>> .join(pl).on(p.FK_DEPUTY.eq(pl.ID))
>>> .where(condition)
>>> .groupBy(p.ID));
>>>
>>> but the result was always this exception:
>>> Caused by: org.postgresql.util.PSQLException: ERROR: table name
>>> "securitydb_user" specified more than once
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
>>> ~[postgresql-42.6.0.jar:42.6.0]
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
>>> ~[postgresql-42.6.0.jar:42.6.0]
>>>
>>> Any ideas?
>>> Thanks alot!
>>>
>>> I search in the master class book but could not find any example
>>> covering joining the same table twice.
>>>
>>> --
>>> 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.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/jooq-user/62d9594b-4c62-48ed-a29d-a07bb89f8d20n%40googlegroups.com
>>> <https://groups.google.com/d/msgid/jooq-user/62d9594b-4c62-48ed-a29d-a07bb89f8d20n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>> .
>>>
>> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/2a99bcdb-a3c5-4ea8-b459-6d2b1256cb85n%40googlegroups.com
> <https://groups.google.com/d/msgid/jooq-user/2a99bcdb-a3c5-4ea8-b459-6d2b1256cb85n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO5q%3DBAULSQvxFLxpHCkReGiJpHZJDj5SbqarjTzLvv-9g%40mail.gmail.com.
