Jooq: Joining the same table twice across a three table join

Question

So i have three tables

Parent_section(id)

Assessment_Question(id,parent_section_id)

Assessment_Answer(id,assessment_question_id)

I am trying to inner join parent_section and assessment_qestion, and left join assessment_question and assessment_Answer

So far joining parent_Section and assessment_question I have this, not sure how to do it for a third table?

    var queryResult= dsl.select()
    .from(PARENT_SECTION)
    .join(ASSESSMENT_QUESTION)
    .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
    .where(PARENT_SECTION.GATE_ID.eq(gateId))
    .fetch()

Answer 1

Just add another join where you would have added another join in plain SQL:

var queryResult = dsl.select()
    .from(PARENT_SECTION)
    .join(ASSESSMENT_QUESTION)
    .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
    .leftJoin(ASSESSMENT_ANSWER)
    .on(ASSESSMENT_QUESTION.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_QUESTION_ID))
    .where(PARENT_SECTION.GATE_ID.eq(gateId))
    .fetch()