Skip to main content

JPQL: outer join on entity beans without association

1 reply [Last post]
imperfect
Offline
Joined: 2006-10-18

hi,

i want to know whether it is possible to write JPQL query, involving outer join on entity beans without association??

e.g. 2 entity beans:

class Employee
{
@Id
String name;

String projectID;

String phone;
....
....
}

class Project
{
@Id
String projectID;

String description;
....
....
}

***each employee can involed in zero or one project
***project can have zero or more employee joined.

the 2 entity beans above don't have any association defined.

what i want to is to write a query to get a list of employee and, if exists,
the project employee involved in.

select e.name, p
from Employee e left join Project p on (e.projectID = p.projectID)

>>i search the document / tutorials that all left join examples involve
>>association.

>>e.g. select xx from XX left join XX.yy

could anybody tell me the answer?

thank you

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
mb124283
Offline
Joined: 2005-06-17

Hi,

is there any issue defining a ManyToOne relationship between Employee and Project? This would make it much easier:
SELECT e.name, p FROM Employee e LEFT JOIN e.project p

I do not have a good solution for the entity classes without relationship. You could run two separate queries. One query returning the employees being involved in a project:
SELECT e.name, p FROM Employee e, Project p WHERE e.projectID = p.projectID
And another query returning the employees without project:
SELECT e.name FROM Employee e WHERE e.projectID IS NULL

I thought about using an OR expression:
SELECT e.name, p FROM Employee e, Project p WHERE e.projectID IS NULL OR e.projectID = p.projectID
But this would combine any employee without a project with all the projects stored in the database and this is certainly not what you want.

Regards Michael