1 Reply Latest reply on Feb 28, 2007 11:47 AM by jlessner

    LEFT JOIN Help!

    justinmiller

      In EJB QL, is it possible to do a LEFT JOIN on two entities that have no declared relationship, thus simulating the ON clause in the sql equivalent??

      From the EJB 3.0 book, they give the example of finding all customers and their phone numbers - whether or not they have them:

      SELECT c.firstName, c.lastName, p.number FROM Customer c LEFT JOIN c.phoneNumbers p

      That works because there is a declared relationship between Customer and Phone. What if I didn't have that relationship and wanted to do something more like this:

      SELECT c.firstName, c.lastName, p.number FROM Customer c LEFT JOIN Phone p WHERE c.someId=p.someId

      Is that even possible?

      Thanks in advance!

      Justin

        • 1. Re: LEFT JOIN Help!
          jlessner

          From what I've understood this is *not* possible with JPA-QL because all outer joins are a special form of relationship joins and therefore require a relationship to refer to. So you only have a chance using SQL in your case. In some special cases you can use sub-selects as a work-around.