[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - LEFT JOIN, EXISTS or WHAT ELSE?

[toni]

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;
...
}

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

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