Jooq select syntax

Question

I have two tables. Each client has 2 addresses. I need to select the client name and string of addresses. I'm confused about jooq syntax, how select basic_address and additional_address for TABLE RESULT

Table CLIENT

id | name | BASIK_OFICE_ID | ADDITIONAL_OFICE_ID

Table OFICE

id | address

TABLE RESULT

name | basic_address | additional_address

public record ClientAndOfficeData(
 String name,
 String basic_address,
 String additional-address) {}
 var content =
 context
 .select(
 CLIENT.NAME,
 OFFICE.ADDRESS,
 OFFICE.ADDRESS)
 .from(CLIENT)
 .innerJoin(OFICE)
 .on(OFFICE.ID.eq(CLIENT.BASIK_OFFICE_ID))
 .or(OFFICE.ID.eq(CLIENT.ADDITIONAL_OFFICE_ID))
 .fetchInto(ClientAndOfficeData.class);
 }

• DarkBee: Please edit your question to specifically and clearly define the problem that you are trying to solve. Additional details — including all relevant code, error messages, and debugging logs — will help readers to better understand your problem and what you are asking.

Answer 1

This isn't really a jOOQ question but a generic SQL question. You're trying to do something like a PIVOT query, but only with a fixed number of 2 columns. So, you have to join the table twice:

Office basic = OFFICE.as("basic");
Office additional = OFFICE.as("additional");
var content = context
 .select(
 CLIENT.NAME,
 basic.ADDRESS,
 additional.ADDRESS)
 .from(CLIENT)
 .leftJoin(basic)
 .on(basic.ID.eq(CLIENT.BASIK_OFFICE_ID))
 .leftJoin(additional)
 .on(additional.ID.eq(CLIENT.BASIK_OFFICE_ID))
 .fetchInto(ClientAndOfficeData::new);

This approach uses type safe table aliases based on your generated code. I used LEFT JOIN instead of INNER JOIN, assuming that the office IDs may be optional.

Another, perhaps simpler approach would be to use implicit joins:

var content = context
 .select(
 CLIENT.NAME,
 CLIENT.basicOffice().ADDRESS,
 CLIENT.additionalOffice().ADDRESS)
 .from(CLIENT)
 .fetchInto(ClientAndOfficeData::new);

This assumes that you have named your foreign keys BASIC_OFFICE and ADDITIONAL_OFFICE. If the keys are named differently, there will be different methods than basicOffice() and additionalOffice().

• Yula: Lukas, thank you so much, the first part of the code worked!!!! I tried for so long to find the right solution. This is incredibly cool, thank you!!!!!!))