JOOQ, Unknown column 'ideas_service.parent_section.id' in 'on clause' when joining multiple tables

Question

So I have five tables I am trying to get back in one query

parent_section(id, gate_id)


Assessment_Question(id,parent_section_id)


Assessment_question_multi_choice_option(id, question_id)


Assessment_answer_Group(id, entity_id)


Assessment_Answer(id,assessment_question_id, assessment_answer_Group_id)

I am trying to list all questions

Error is Unknown column 'ideas_service.parent_section.id' in 'on clause'

Here is my query

        dsl.select()
        .from(PARENT_SECTION,ASSESSMENT_ANSWER_GROUP)
        .join(ASSESSMENT_QUESTION)
        .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
        .leftJoin(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION)
        .on(
            ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION.ASSESSMENT_QUESTION_ID.eq(
                ASSESSMENT_QUESTION.ID))
        .join(ASSESSMENT_ANSWER)
        .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
        .where(PARENT_SECTION.GATE_ID.eq(gateId))
        .fetch()

Previously when I was only trying to query the table for three tables, parent_Section_assessment_question and assessment_question_multi_choice, my query was like this and it worked

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

How should I modify the working query to also list a join between a assessment_Answer_Group( where assessment_answer_Group.entity_id = x) on the questions assessment_answer.answer_group_id

To clarify on the joining structure

I am doing an inner join between parent_section and assessment_Question and a left join between assessment_question and assessment_multi_choice_option

The join between answer_group and assessment_answer is separate from those three tables, it should be where assessment_answer_group.entity_id = x and join on assessment_Answer.answer_Group_id = assessment_answer_Grorup.id

edit: After akinas comments my query is now this, it finds the assessment_answer_group but it does not find any assessment_answers

    var queryResult =
    dsl.select()
      //below line is cross join with an always true join coniditon
        .from(PARENT_SECTION.join(ASSESSMENT_ANSWER_GROUP).on(true))
        .join(ASSESSMENT_QUESTION)
        .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
        .leftJoin(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION)
        .on(
            ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION.ASSESSMENT_QUESTION_ID.eq(
                ASSESSMENT_QUESTION.ID))
        .join(ASSESSMENT_ANSWER)
        .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
        .where(PARENT_SECTION.GATE_ID.eq(gateId))

Answer 1

Why are you getting the syntax error

The reason why you're getting this error:

Error is Unknown column 'ideas_service.parent_section.id' in 'on clause'

Is because you're joining ASSESSMENT_QUESTION to ASSESSMENT_ANSWER_GROUP, not to PARENT_SECTION in your jOOQ query:

dsl.select()
   .from(PARENT_SECTION,ASSESSMENT_ANSWER_GROUP)
   .join(ASSESSMENT_QUESTION)
   .on(PARENT_SECTION.ID.eq(ASSESSMENT_QUESTION.PARENT_SECTION_ID))
   // ...

As others have pointed out in the comments, you're mixing comma separated from lists with ANSI JOIN syntax. This is hardly ever recommended, both in SQL, or in jOOQ. In jOOQ, your above query corresponds to this SQL:

SELECT *
FROM PARENT_SECTION, (
  ASSESSMENT_ANSWER_GROUP 
    JOIN ASSESSMENT_QUESTION 
      ON PARENT_SECTION.ID = ASSESSMENT_QUESTION.PARENT_ID
  )
..

As you can see, the join expression doesn't make any sense because you're joining two tables using a predicate that references a table that is not in scope. I recently wrote a blog post that might help understanding SQL JOIN syntax by adding such parentheses. I think the parentheses definitely help here.

Here's my advice on getting joins right:

• Never mix table lists with ANSI joins, it's usually just trouble (although, it can be useful in edge cases). If you need outer joins, you cannot use table lists in most SQL dialects (except for Oracle), so you need ANSI joins anyway. Stick to them.
• Be careful of mixing outer joins with inner joins. Joins are operators, and it's important to be aware of their associativity

Cartesian products

But you have another problem. You cannot easily join several to-many relationships in SQL, or you'll get cartesian products. Meaning:

• A PARENT_SECTION can have many ASSESSMENT_QUESTION, which can have many ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION. These are 2 levels of to-many relationships, which will duplicate the PARENT_SECTION per ASSESSMENT_QUESTION and again the ASSESSMENT_QUESTION per ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION. This repeated duplication is OK, because you're going down one branch of your join tree.
• However, an ASSESSMENT_QUESTION also has several ASSESSMENT_ANSWER, because that's another to-many relationship. This is causing problems.

So, you'll end up with a cartesian product between ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION and ASSESSMENT_ANSWER, as you're traversing two separate to-many branches of your join tree, which is definitely not what you want.

While your 5-join query might eventually be right syntactically, it will still be wrong semantically!

Getting it right

You will have to resort to using either XML/JSON (available from jOOQ 3.14), or run (at least) 2 queries:

// Query 1 producing ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION
dsl.select()
   .from(PARENT_SECTION)
   .join(ASSESSMENT_QUESTION)
     .on(ASSESSMENT_QUESTION.PARENT_SECTION_ID.eq(PARENT_SECTION.ID))
   .leftJoin(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION)
     .on(ASSESSMENT_QUESTION_MULTI_CHOICE_OPTION.ASSESSMENT_QUESTION_ID
       .eq(ASSESSMENT_QUESTION.ID))
   .where(PARENT_SECTION.GATE_ID.eq(gateId))
   .fetch();

// Query 2 producing ASSESSMENT_ANSWER and ASSESSMENT_ANSWER_GROUP
dsl.select()
   .from(ASSESSMENT_ANSWER)
   .join(ASSESSMENT_ANSWER_GROUP)
     .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
   .where(ASSESSMENT_ANSWER.ASSESSMENT_QUESTION_ID.in(
     select(ASSESSMENT_QUESTION.ID)
     .from(ASSESSMENT_QUESTION)
     .join(PARENT_SECTION)
     .on(ASSESSMENT_QUESTION.PARENT_SECTION_ID.eq(PARENT_SECTION.ID))
     .where(PARENT_SECTION.GATE_ID.eq(gateId))
   ))
   .fetch();

You can then assemble the results together in your client. Notice the second query doesn't need to join PARENT_SECTION and ASSESSMENT_QUESTION anymore. A semi join (implemented using the IN predicate) will suffice

Using implicit joins

jOOQ supports implicit joins for to-one relationships. The above Query 2 could also be written like this, which is perhaps a bit simpler (although I do recommend you study "real" joins first):

dsl.select()
   .from(ASSESSMENT_ANSWER)
   .join(ASSESSMENT_ANSWER_GROUP)
     .on(ASSESSMENT_ANSWER_GROUP.ID.eq(ASSESSMENT_ANSWER.ASSESSMENT_ANSWER_GROUP_ID))
   .where(ASSESSMENT_ANSWER.assessmentQuestion().parentSection().GATE_ID.eq(gateId))
   .fetch();