0 Replies Latest reply on Dec 9, 2006 3:15 PM by Toni Beckman

    LEFT JOIN, EXISTS or WHAT ELSE?

    Toni Beckman Novice

      Hi,

      I have three Entity Beans: Client, Person and Cooperation of which the later may connect/reference the first two using a @OneToOne Relationship.

      I'm looking for a EJB QL Query, which will return all the Person's, which are not referenced/contained in Cooperation's.

      How would the Query look like?

      All the following queries produced errors or did not return anything:

      SELECT x.p FROM (SELECT p, cm FROM Cooperation AS cm LEFT JOIN cm.partner p) as x WHERE x.cm IS NULL

      OR

      FROM Partner p WHERE NOT EXISTS (SELECT cm FROM Cooperation cm WHERE cm.partner IS NOT NULL AND cm.partner=p)

      public class Person implements java.io.Serializable
      {
      @Id @NotNull @Length(min=5, max=40)
      String hostname;

      String firstName;
      String lastName;
      String street;
      ...
      }

      public class Client extends Person implements java.io.Serializable
      {
      @OneToOne(cascade={CascadeType.ALL})
      @PrimaryKeyJoinColumn
      EmailAccount emailAccount;
      ...
      }

      public class Cooperation implements Serializable
      {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      long id;

      @OneToOne(cascade={CascadeType.ALL})
      Client client;

      @OneToOne(cascade={CascadeType.ALL})
      Partner partner;
      ...
      }