gistfile1.txt

Tables:

CREATE TABLE "activities" (
  "id" INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
);

CREATE TABLE "interests" (
  "id"        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  "name"      VARCHAR(255) NOT NULL,
  "parent_id" INT UNSIGNED,
  PRIMARY KEY ("id"),
  FOREIGN KEY ("parent_id") REFERENCES "interests" ("id")
);

CREATE TABLE "activities_interests" (
  "activity_id" INT UNSIGNED NOT NULL,
  "interest_id" INT UNSIGNED NOT NULL,
  PRIMARY KEY ("activity_id", "interest_id"),
  FOREIGN KEY ("activity_id") REFERENCES "activities" ("id"),
  FOREIGN KEY ("interest_id") REFERENCES "interests" ("id")
);

Query:

    ctx.select().from(ACTIVITIES_INTERESTS).join(INTERESTS).on(
        ACTIVITIES_INTERESTS.INTEREST_ID.equal(INTERESTS.ID)).where(
        ACTIVITIES_INTERESTS.ACTIVITY_ID.equal(id)).fetch();

Record:

+-----------+-----------+----+------+---------+
|activity_id|interest_id|  id|name  |parent_id|
+-----------+-----------+----+------+---------+
|          1|          2|   2|Math  |        1|
|          1|         48|  48|Travel|   {null}|
+-----------+-----------+----+------+---------+

System.out.println("ACTIVITIES.ID -> " + rec.getValue(ACTIVITIES.ID));
System.out.println("INTERESTS.ID -> " + rec.getValue(INTERESTS.ID));

ACTIVITIES.ID -> 2
INTERESTS.ID -> 2

ACTIVITIES.ID -> 48
INTERESTS.ID -> 48

Fields:

"xxx"."activities_interests"."activity_id"
"xxx"."activities_interests"."interest_id"
"xxx"."interests"."id"
"xxx"."interests"."name"
"xxx"."interests"."parent_id"