DetachedCriteria itemCrit = DetachedCriteria.forClass(Item.class);
itemCrit.setProjection(Projections.projectionList().add( Projections.property( "subCategory" )) );
DetachedCriteria outletCritria = criteria.createCriteria("collection");
outletCritria.add( Expression.ge( "endDate", new Date() ));
outletCritria.add( Expression.le( "startDate", new Date() ));
Criteria crit = hibernateSession.createCriteria( SubCategory.class  );
crit.add( Property.forName("id").in(  itemCrit ));
crit.addOrder( Order.desc("sortOrder") );

Hibernate: select this_.CategoryId as CategoryId8_1_, this_.SortOrder as SortOrder8_1_, this_.Identifier as Identifier8_1_, this_.CachedBalance as CachedBa5_8_1_, this_.ApproximateWeight as Approxim6_8_1_, this_.ParentId as ParentId8_1_, maincatego2_.CategoryId as CategoryId8_0_, maincatego2_.SortOrder as SortOrder8_0_, maincatego2_.Identifier as Identifier8_0_, maincatego2_.CachedBalance as CachedBa5_8_0_ from Categories this_ left outer join Categories maincatego2_ on this_.ParentId=maincatego2_.CategoryId where this_.Type='SUB' and this_.CategoryId in (select this0__.CategoryId as y0_ from Items this0__ where this0__.BrandId=? and collection1_.EndDate>=? and collection1_.StartDate<=?) order by this_.SortOrder desc
WARN  - JDBCExceptionReporter      - SQL Error: 1054, SQLState: 42S22
ERROR - JDBCExceptionReporter      - Unknown column 'collection1_.EndDate' in 'where clause'

package se.boardstore.entities.item;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import se.boardstore.entities.brand.Brand;
import se.boardstore.entities.category.SubCategory;
import se.boardstore.entities.collection.Collection;

@Entity
@Table( name = "Items" )
public class Item {
   
   @Id
   @GeneratedValue
   @Column( name = "ItemId" )
   private Long id;
   @ManyToOne( fetch = FetchType.LAZY, cascade = CascadeType.REFRESH )
   @JoinColumn( nullable=false, name = "CategoryId" )      
   private SubCategory subCategory;
   @ManyToOne( fetch = FetchType.LAZY, cascade = CascadeType.REFRESH )
   @JoinColumn( nullable=false, name = "BrandId" )      
   private Brand brand;
   @ManyToOne( fetch = FetchType.LAZY, cascade = CascadeType.REFRESH )
   @JoinColumn( nullable=false, name = "CollectionId" )      
   private Collection collection;
   /** TODO: Add colour story here */
   @Column( nullable=false, name = "Identifier" )
   private String identifier;
   @Column( nullable=false, name = "Price" )
   private float price;
   @Column( nullable=false, name = "MemberPrice" )
   private float memberPrice;
   @Column( nullable=false, name = "Value" )
   private float value;
   @Column( nullable=false, name = "Discount")
   private float discount = 0;
   @Column( name = "CachedBalance" )
   private Long cachedBalance;
   @Column( name= "IsInPromotion" )
   private boolean inPromotion;
   @Column( nullable=false, name = "EarliestDeliveryDate" )
   @Temporal(TemporalType.TIMESTAMP)
   private java.util.Date earliestDeliveryDate;
   @Column( name = "RefilledDate" )
   @Temporal(TemporalType.TIMESTAMP)
   private java.util.Date refilledDate;
   @Column( name = "IsMale", nullable = false )
   private boolean male;
   @Column( name = "IsFemale", nullable = false )
   private boolean female;
   @Column( name = "IsJunior", nullable = false )
   private boolean junior;
   public SubCategory getSubCategory() {
      return subCategory;
   }
   public void setSubCategory(SubCategory subCategory) {
      this.subCategory = subCategory;
   }
   public Brand getBrand() {
      return brand;
   }
   public void setBrand(Brand brand) {
      this.brand = brand;
   }
   public Collection getCollection() {
      return collection;
   }
   public void setCollection(Collection collection) {
      this.collection = collection;
   }
   public String getIdentifier() {
      return identifier;
   }
   public void setIdentifier(String identifier) {
      this.identifier = identifier;
   }
   public float getPrice() {
      return price;
   }
   public void setPrice(float price) {
      this.price = price;
   }
   public float getMemberPrice() {
      return memberPrice;
   }
   public void setMemberPrice(float memberPrice) {
      this.memberPrice = memberPrice;
   }
   public float getValue() {
      return value;
   }
   public void setValue(float value) {
      this.value = value;
   }
   public float getDiscount() {
      return discount;
   }
   public void setDiscount(float discount) {
      this.discount = discount;
   }
   public Long getCachedBalance() {
      return cachedBalance;
   }
   public void setCachedBalance(Long cachedBalance) {
      this.cachedBalance = cachedBalance;
   }
   public boolean isInPromotion() {
      return inPromotion;
   }
   public void setInPromotion(boolean inPromotion) {
      this.inPromotion = inPromotion;
   }
   public java.util.Date getEarliestDeliveryDate() {
      return earliestDeliveryDate;
   }
   public void setEarliestDeliveryDate(java.util.Date earliestDeliveryDate) {
      this.earliestDeliveryDate = earliestDeliveryDate;
   }
   public java.util.Date getRefilledDate() {
      return refilledDate;
   }
   public void setRefilledDate(java.util.Date refilledDate) {
      this.refilledDate = refilledDate;
   }
   public boolean isMale() {
      return male;
   }
   public void setMale(boolean male) {
      this.male = male;
   }
   public boolean isFemale() {
      return female;
   }
   public void setFemale(boolean female) {
      this.female = female;
   }
   public boolean isJunior() {
      return junior;
   }
   public void setJunior(boolean junior) {
      this.junior = junior;
   }
   public Long getId() {
      return id;
   }

}

package se.boardstore.entities.collection;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table( name = "Collections" )
public class Collection {
   
   @Id
   @GeneratedValue
   @Column( name = "CollectionId")
   private Long id;
   @Column( name = "StartDate" )
   @Temporal(TemporalType.TIMESTAMP)
   private java.util.Date startDate;
   @Temporal(TemporalType.TIMESTAMP)
   @Column( name = "EndDate" )
   private java.util.Date endDate;
   @Column( name = "Discount" )
   private float discount;
   @Column( name = "Identifier" )
   private String identifier;

   public String getIdentifier() {
      return identifier;
   }
   public void setIdentifier(String identifier) {
      this.identifier = identifier;
   }
   public java.util.Date getStartDate() {
      return startDate;
   }
   public void setStartDate(java.util.Date startDate) {
      this.startDate = startDate;
   }
   public java.util.Date getEndDate() {
      return endDate;
   }
   public void setEndDate(java.util.Date endDate) {
      this.endDate = endDate;
   }
   public float getDiscount() {
      return discount;
   }
   public void setDiscount(float discount) {
      this.discount = discount;
   }
   public Long getId() {
      return id;
   }
}

package se.boardstore.entities.category;

import javax.persistence.Column;
import javax.persistence.DiscriminatorColumn;
import javax.persistence.DiscriminatorValue;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.Table;


@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn( name = "Type" )
@DiscriminatorValue( "CATEGORY" )
@Table( name = "Categories" )
public class Category {
    @Id
    @GeneratedValue
    @Column( name="CategoryId")
    private Long id;
    @Column(nullable=false,name="SortOrder")
    private Long sortOrder;
    @Column( nullable=false, name="Identifier")
    private  String identifier;
    @Column( name = "CachedBalance" )
    private Long cachedBalance;

    public Long getCachedBalance() {
      return cachedBalance;
   }

   public void setCachedBalance(Long cachedBalance) {
      this.cachedBalance = cachedBalance;
   }

   public String getIdentifier() {
      return identifier;
    }

    public void setIdentifier(String identifier) {
      this.identifier = identifier;
    }

    public Long getId() {
      return id;
    }

   public Long getSortOrder() {
      return sortOrder;
   }

   public void setSortOrder(Long sortOrder) {
      this.sortOrder = sortOrder;
   }
}

package se.boardstore.entities.category;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.DiscriminatorValue;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;

import se.boardstore.entities.item.Item;

@Entity
@DiscriminatorValue( "SUB" )
public class SubCategory extends Category{
   @Column( name = "ApproximateWeight" )
   private Long approximateWeight;
   
   @ManyToOne(targetEntity=MainCategory.class)
   @JoinColumn( name = "ParentId")
   private MainCategory mainCategory;
   
   @OneToMany( mappedBy = "subCategory", fetch = FetchType.LAZY, cascade = CascadeType.REFRESH )//fetch = FetchType.EAGER ) //A brand can have 0 - * Items
   @JoinColumn( name = "CategoryId" )
   private List<Item> items = new ArrayList<Item>();

   public MainCategory getMainCategory() {
      return mainCategory;
   }

   public void setMainCategory(MainCategory mainCategory) {
      this.mainCategory = mainCategory;
   }

   public Long getApproximateWeight() {
      return approximateWeight;
   }

   public void setApproximateWeight(Long approximateWeight) {
      this.approximateWeight = approximateWeight;
   }

   public List<Item> getItems() {
      return items;
   }

   public void setItems(List<Item> items) {
      this.items = items;
   }
}

DetachedCriteria.forClass(SubCategory.class)
    .createAlias("items","i")
    .createAlias("i.collection","c")
    .add(Restrictions.ge("c.startDate",yourStartDate))
    .add(Restrictions.le("c.endDate",yourEndDate))
    .add(Restrictions.gt("cachedBalance",1));

setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)

setProjection(Projections.distinct(Property.forName("id")))

select distinct....

DetachedCriteria.forClass(SubCategory.class)
   .add(Restrictions.in("id",distinctIdCriteria));

SELECT s.* FROM Categories s WHERE s.CategoryId IN( SELECT i.CategoryId FROM Items i, Collections c
WHERE   c.CollectionId = i.CollectionId AND
i.CachedBalance > 0 AND c.StartDate <= now() AND c.EndDate >= now() )

SELECT s.* FROM Categories s, Items i, Collections c  WHERE s.Type = 'SUB'
AND s.CategoryId = i.CategoryId AND c.CollectionId = i.CollectionId AND
i.CachedBalance > 0 AND c.StartDate <= now() AND c.EndDate >= now()
GROUP BY s.CategoryId

DetachedCriteria crit = DetachedCriteria.forClass(SubCategory.class)
.createAlias("items","i")
.createAlias("i.collection","c")
.add(Restrictions.ge("i.cachedBalance", new Long( 1 )))
.setProjection(Projections.distinct(Property.forName("id")));

      
List<SubCategory> subs = crit.getExecutableCriteria(hibernateSession).list();