title: Hibernate Join Query Criteria and getting the Join Table with both the columns
body: <p>I have a table <em>cartitems</em> where the items added to the cart are stored in. It has columns:</p>
  
  <p>id (Primary Key), sku, quantity, userId, status, size and couple of other columns</p>
  
  <p>I also have a <em>productAvailability</em> table in which we store Inventory levels and where I have following columns:</p>
  
  <p>sku, size , quantity, BatchId (Primary Key)</p>
  
  <p>Also <em>product</em> table has one-to-many relationships with <em>productAvailability</em> table and <em>cartitems</em> table both on the <em>sku</em> key</p>
  
  <p>Now, for each cartitems with status = 1 and userId=x we need to check the availability of the product and if available, I need the CartItems row and the BatchId of the product:</p>
  
  <p>I got till here:</p>
  
  <pre><code> Criteria criteria = session.createCriteria(CartItems.class,"cartitems");
              criteria.add(Restrictions.eq("userId", userId));
              criteria.add(Restrictions.eq("status", status));
              criteria.createAlias("product", "product");
              criteria.createAlias("product.productAvailability", "productavailability");
              criteria.add(Restrictions.eqProperty("productavailability.size", "cartitems.size"));
              criteria.add(Restrictions.geProperty("productavailability.quantity", "cartitems.quantity"));
                      List&lt;CartItems&gt; cartItems =  criteria.list();
  </code></pre>
  
  <p>The above basically does following:</p>
  
  <pre><code>select * from cartItems 
      where productAvailability.quantity &gt;= cartitems.quantity and
            productAvailability.size = cartitems.size and
            productAvailability.sku = cartitems.sku and
            cartitems.userId = ? and
            cartitems.status = ?
  </code></pre>
  
  <p>This way I cannot get the BatchId. I tried using JOIN FETCHTYPE but it still loads productAvailability table with all sizes of an sku. Is there any method to get both cartItems and corresponding BatchIds for each of the cartItems? </p>