CODEWITHSUNDEEP
javajpajpql
acroscene
acroscene

Reputation: 1067

Join multiple tables with one JPQL query

I got this sql-query I want to create as query in JPQL but I cannot get it right. I got a manytoone relationship between QuestionAnswers and QuizQuestions:

SQL:

SELECT quizName, question, answer FROM Quiz 
JOIN QuizQuestions on Quiz.quizId = QuizQuestions.Quiz_QuizId 
JOIN QuestionAnswers on QuizQuestions.questionId = QuestionAnswers.question_questionId 
WHERE quiz.quizId = 1;

JPQL query:

Query query = entityManager.createQuery("SELECT q.quizName, f.question, a.answer FROM Quiz q, QuizQuestions f, QuestionAnswers a LEFT JOIN QuestionAnswers ON f.questionId=a.question.questionId");

I get syntax error in Intellij.

What can be wrong?

Im using EclipseLink

EDIT solved it like this with just one join:

Query query = entityManager.createQuery("SELECT f.quiz.quizName FROM QuizQuestions f JOIN QuestionAnswers qa WHERE f.questionId = qa.question.questionId");

Upvotes: 8

Views: 31189

Answers (1)

Dherik
Dherik

Reputation: 19110

Probably, you are looking for a JPQL like this:

SELECT quiz.name, quizquestion.question, questionasnswer.answer FROM Quiz quiz
JOIN quiz.quizQuestions quizquestion
JOIN quizquestion.questionAnswers questionasnswer
WHERE quiz.id = 1;

I would evict solutions with qa.question.questionId (tableA.tableB.column), because the JPA framework not always generate nice SQLs from this. Always explicit the JOINs in the JPQL.

Upvotes: 6

Related Questions