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

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#... Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...