Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ejb-ql question (extending the constraint in an outer join)

 
jeroen dijkmeijer
Ranch Hand
Posts: 132
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I would like to rewrite "select * from role rl left outer join actor a on (rl.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 be performing many roles.
a role might be performed by many persons.
a person having a role is an actor.
database shows like:
Code:

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 person 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.
 
jeroen dijkmeijer
Ranch Hand
Posts: 132
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Found it the with clause was my friend:

But i'm afraid its not ejb-ql(?)
regards,
Jeroen
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic