Join same table twice with PostgreSQL

[Stefan Sator]

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.

[Lukas Eder]

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

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

[Stefan Sator]

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)));

[Lukas Eder]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/2a99bcdb-a3c5-4ea8-b459-6d2b1256cb85n%40googlegroups.com.

[Stefan Sator]

Yes that works. 
It was already clear from your answer.

Thanks!