[jboss-user] [EJB 3.0] - ejb-ql question (extending the constraint in an outer join)

[jdijkmeijer]

Hi,
I would like to rewrite "select * from role r left outer join actor a on (r.id = a.roleid and a.personid=1);" in a ejb-ql form.

Some more background info:
the query intends to serve a usecase for retrieving a many to many relation, modeled in 2 one-to-mny relations and a join table:
a person may performing many roles.
a role might be performed by many persons.
a person having a role is an actor.
database shows like:

  | create table person (id, number(10));
  | create table role (id, number(10));
  | create table actor (personid Number(10), roleid number(10));
  | 
The query should retrieve ALL roles with, the associated actors if present for a given person.
I first tried a outer left join query together with a where statement but that failed, because not all roles where retrieved or more than the present actors for the particular subject where retrieved. In oracle the query above does what's expected, but i see no way to have a double condition in the join statement in ejb-ql, i'm not even sure its ansi sql.
Any help would be appreciated.
regards,
Jeroen.


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4086719#4086719

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4086719