Hi there,
I have a question regarding JPA Query
I have a DEPARTMENT entity with a one-to-many relationship to an EMPLOYEE
entity. Say I want to get "departments with a department number of 100 and
containing EMPLOYEES with more than 15 years of service".
Is it possible to get back a single result containing the DEPARTMENT object; and
its relationship collection list consisting of only the EMPLOYEES matching the
criteria?
I've tried creating various queries using joins, but they always return me back
the DEPARTMENT I want, but the its employee list contains ALL of the employees
belonging to that department, not just the ones matching the "15 years of
service" filter.
For example:
select d from Department d LEFT JOIN d.employeeCollection e WHERE d.deptno = 100
AND e.yearsOfService >= 15
This returns one result - a Department with ALL of the employees that belong to
that department (not just the ones matching e.yearsOfService >= 15)
I also tried this:
select d, e from Department d LEFT JOIN d.employeeCollection e WHERE d.deptno =
100 AND e.yearsOfService >= 15
And that returns 3 results, each an array containing a Department and Employee.
This is giving me the right data (there are 3 employees that match the
criteria), but I really just want to get back a single department result
containing the filtered employees. Is this possible?
I'd appreciate any help; I realize I was blathering a way a bit at the end there :)
Thanks
Tom