Issue
Not easy to explain.
A simple example :
An employee
table, with many fields.
A city
table, where an employee goes regurlarly.
A speciality
table, in which an employee gets a good knowledge.
Two relation tables for that : emp_city
and emp_spec
.
Java EmpCity class attributes : int id, Employee-class emp, City-class city
.
Same for EmpSpec class : int id, Employee-class emp, Spec-class spec
Of course the class attributes are annotated @ManyToOne
I would like to fetch the List of EmpCity classes for employees who get the computer speciality and regurlarly in London :
@NameQuery(name = "findEmpCountryByCityAndSpec", query = "SELECT ec FROM EmpCity LEFT JOIN FETCH ec.emp WHERE ec.city.name = 'London' AND ....")
?
So I can't see how to join with the EmpSpec table to select computer.
Thank you.
Solution
Not sure if I fully understand your model (posting the entity classes would help), but how about the following query:
SELECT ec
FROM EmpCity
JOIN FETCH ec.emp e
JOIN EmpSpec es ON es.emp = e
WHERE ec.city.name = 'London'
AND es.spec.name = 'computer'
Answered By - Christian Beikov
Answer Checked By - Mary Flores (JavaFixing Volunteer)