0 Replies Latest reply on Dec 20, 2006 6:42 AM by Toni Beckman

    LEFT JOIN Problem

    Toni Beckman Novice

      Hi,

      I have two entities: Email and Person. Each Email entity is referencing a Person entity, but each Person entity is unaware of that.

      I'm looking for an EJB QL statement, which will return for each Person how many Email Entities reference it. Or how many emails a Person has written.

      I have a hard time coming up with a QL statement, because Person is not referencing back to Email, so I have no collection field to reference in a LEFT JOIN. Of course I could introduce one and the problem would be solved, but I'm trying to learn how do do this without one.

      I have tried the following statements, but they all return 0 rows:

      // I'am not sure if this is correct, because I'm joining on the emails. So if I have no emails for a person, then those person are excluded, I guess.
      Select pe, count(a) FROM Email e LEFT JOIN e.person AS pe GROUP BY pe.id;

      // So that's why I'm trying to join on the Person by pulling it into the equation, but I'm not sure, if this will actually join each Email on each Person?!
      Select pe, count(e) FROM Person p, Email e JOIN e.person AS pe GROUP BY pe.id;

      What ist the correct QL statement for this problem?