This is the sql that is output from Hibernate log:
Code:
    /* select
        count(distinct us),
        count(distinct u),
        count(distinct q)
    from
        UserSession us ,
        User u,
        UserSession q
    where
        q.question is not null */ select
            count(distinct usersessio0_.USERSESSION_ID) as col_0_0_,
            count(distinct user1_.USER_ID) as col_1_0_,
            count(distinct usersessio2_.USERSESSION_ID) as col_2_0_
        from
            USERSESSION usersessio0_,
            USER user1_,
            USERSESSION usersessio2_
        where
            usersessio2_.QUESTION_ID is not null limit ?
These are the two Entities (with relevant fields included only):
Code:
@Entity
@Table(name = "USER")
@org.hibernate.annotations.Entity(selectBeforeUpdate = true)
public class User implements Serializable, Comparable   {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   @Column(name = "USER_ID")
   private Long id;   
   
   @Column(name = "USERNAME", length = 20, nullable = false, unique = true)   
   private String username;
   
    @OneToOne(mappedBy = "user")
    private UserSession usersession; 
    
    
        
   public User() {}
      ....etc
}
@Entity
@Table(name = "USERSESSION")
public class UserSession implements Serializable, Comparable   {
   
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   @Column(name = "USERSESSION_ID")
   private Long id;   
   
   @OneToOne
   @JoinColumn(name = "USER_ID")   
   private User user;
   
   @OneToOne
   @JoinColumn(name = "QUESTION_ID")   
   private Question question;
   
     
         public UserSession() {}
     .... etc
}
I know the query is not returning what I expect because When there is one user in the user table, and that same user has an entry in the usersession table, the query should return 1,1,0 (Because usersession.question_id will be null).  But the query is always returning 0,0,0.  Obviously the first two counts are not correct.
What I want is simply:
A count of all rows in the user table
A count of all rows in the usersession table
A count of all rows in the usersession table where question_id is not null
I appreciate any help you can give to help me understand this better.  Thanks!
John