title: Hibernate/SQL: Selecting records only if exist in one-to-many relationship, returns too many
body: <p>In my app, I have 2 tables, Settlement Result and Settlement State
  Settlement Result contains some basic data like name, type etc.
  SettlementState is the "many" side of relatioship with Settlement Result and consist of Settlement Result PK and Status as Many-To-One ID as PK and a date. Example data:</p>
  
  <pre><code>Settlement Result
  ------------------------------
  ID| Name      | Sth
  ------------------------------
  1 | Some Name | Something more
  2 | Name2     | more2
  
  Settlement State
  ----------
  Result's ID | StatusId     | Date 
  ------------------------------
  1           | 1            | some date
  1           | 2            | date
  1           | 3            | date
  2           | 1            | date
  </code></pre>
  
  <p>Now I wish to select with HQL/Plain SQL that rows from Settlement Result, that have for example Status Id == 3, but not any with higher ID. </p>
  
  <p>There are few possible statuses:</p>
  
  <pre><code>Status ID | Desc
  -----------------
  1 | Created
  2 | Confirmed
  3 | Accepted
  4 | Rejected
  </code></pre>
  
  <p>When we are creating SettlementResult, there's always some "workflow". Firstly, Result has status Created (ID 1). Then it can be Rejected(ID 4) or Confirmed (ID 2). Then again we can Accept it or Reject.</p>
  
  <p>So one SettlementResult can have SettlementStates with Status ID of 1,2,4 (Created, Confirmed, but not Accepted=Rejected).</p>
  
  <p>The problem is, that I want to select only those SettlementResults which have certain status (for examle Created)."</p>
  
  <p>With HQL query like this:</p>
  
  <pre><code>Query query = session.createSQLQuery( "select distinct s from SettlementResult s join s.settlementStates states where states.settlementStatePK.status.statusId == 1" );
  </code></pre>
  
  <p>It returns every Settlement Result, even those with Statuses 1,2,3 (cause collection contains the one with ID equal to created).</p>
  
  <p>Is it possible to select ONLY those Settlement Results which have ONLY certain status for example if we want Created[ID 1], we get all with Settlement State status of 1 only, without 2,3 or 4 status. When we choose to select those with status id 3, we can accept if it has Settlement State with status id = 1,2,3 but NOT 4. Some kind of <code>max[status.id]</code>?</p>
  
  <pre><code>@Entity
  @Table(name = "SETTLEMENT_STATE")
  public class SettlementState
  {
      @EmbeddedId
      private SettlementStatePK settlementStatePK;
  
      @Column(name = "DESCRIPTION")
      private String description;
  
      @Column(name = "STATUS_DTTM")
      private Date statusDate;
  }
  
  @Embeddable
  public class SettlementStatePK implements Serializable
  {
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = "STATUS_ID")
      private Status status;
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = "SETTLEMENT_RESULT_ID")
      private SettlementResult settlementResult;
  }
  
  @Entity
  @Table(name = "SETTLEMENT_RESULT")
  public class SettlementResult implements Serializable
  {
      @Id
      @Column(name = "SETTLEMENT_RESULT_ID")
      private Long id;
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = "STATUS_ID")
      private Status status;
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = "MODEL_GROUP_ID")
      private SettlementModelGroup settlementModelGroup;
  
      @Column(name = "\"MODE\"")
      private Integer mode;
  
      @Column(name = "CREATED_DTTM")
      private Date createdDate;
  
      @Column(name = "DESCRIPTION")
      private String description;
  
      @OneToMany(fetch = FetchType.LAZY, mappedBy = "settlementStatePK.settlementResult")
      private List&lt;SettlementState&gt; settlementStates;
  }
  </code></pre>