Reputation: 1067
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: 31188
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