Hello Experts,

I want to code below SQL query using Criteria

SELECT COUNT (DISTINCT cc.cntn_id) FROM Category c1, Category c2 ,Content_Category cc
WHERE cc.cat_id = c1.cat_id
AND c1.prnt_id = c2.cat_id
AND c2.srvc_id = 200

(OR)

SELECT COUNT(DISTINCT this_.CNTN_ID) AS y0_
FROM content_category this_
inner join category c1 ON this_.cat_id=c1.CAT_ID
inner join Category c2 ON c1.prnt_id=c2.cat_id
WHERE c2.srvc_id=200

Note: Without using IN, EXISTS, Only thru' joins

Here is my code:

Criteria crit = session.createCriteria(ContentCategory.class, "cc")
.setProjection(Projections.countDistinct("cc.id.contentId"))
.add(Restrictions.eqProperty("cc.id.categoryId","c1.id"))
.createAlias("cc.category.subCategories", "c1")
.add(Restrictions.eqProperty("c1.category","c2.id"))
.createAlias("cc.category", "c2")
.add(Restrictions.eq("c2.service.id",srv.getId()))
.setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY );


The output of hibernate generated query is:

select count(distinct this_.CNTN_ID) as y0_ from content_category this_ inner join category c2x2_ on this_.cat_id=c2x2_.CAT_ID inner join Category c1x1_ on c2x2_.CAT_ID=c1x1_.cat_id where this_.CAT_ID=c1x1_.CAT_ID and c1x1_.PRNT_ID=c2x2_.CAT_ID and c2x2_.srvc_id=?


I tried many ways, but failed to get my query
Please help me to get that query using Criteria...

Thanks,
Senthil