CODEWITHSUNDEEP
sqldatabasejoinjpql
levtatarov
levtatarov

Reputation: 1712

JPQL - avoid using join

i have 3 tables: action, department, employee

action (
  id
  employee_id
  name
)

employee(
  id
  department_id
)

department (
  id
  name
)

i'm using the following query:

select a.id, a.name, d.name from action a, employee e, department d where         a.employee_id=e.id and e.department_id=d.id

but in the case of an action without employee_id i don't get those rows in the result

is there a way to overcome this without using left join?

tried so far (didn't work):

select a.id, a.name, d.name from action a, employee e, department d where         (a.employee_id=e.id and e.department_id=d.id) or a.employee_id is null

Upvotes: 0

Views: 429

Answers (1)

JB Nizet
JB Nizet

Reputation: 692071

You missed the whole point of JPA: having objects associated with other objects directly, rather than objects holding IDs of other objects.

You should have an Action entity with a ManyToOne association to an Employee entity, which would have a ManyToOne association to a Department entity. You should NOT have an employeeId field in Action. And you should NOT have a departmentId field in Employee.

This would allow you, for example, to get the department name of an action by simply calling

action.getEmployee().getDepartment().getName()

And you would be able to use left joins in your query, which would become

select a.id, a.name, d.name
from Action a
left join a.employee e
left join e.department d

and would give you the expected result

Upvotes: 3

Related Questions