I'm trying to combine a CROSS JOIN with a LEFT JOIN. The last one requires a WITH clause that references one of tables used in the CROSS JOIN. For example, I want to list students, questions of an exam, and their answers to them. Some answers may not exist, thus I'm using a LEFT JOIN:
SELECT s.studentId, q.questionId, sa.answer
FROM Student s, Question q
LEFT JOIN s.studentAnswerList AS sa WITH sa.question.questionId = q.questionId
This causes an exception: javax.ejb.EJBException: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements .
The entities are really simple. These are their attributes:
I've been suffering from the same problem in both 3.x and 4.x versions of Hibernate.
JDK 7.0 Update 4
JBoss AS 7.0.1 Final