Select statement in from clause in Hibernate

I have three tables named:

• schedule (id, status)
• criteis (id, name, number, bank)
• info (id, compleInfo)

id is common for all three tables.

My req is that i want bank, number from crities开发者_高级运维 table & compleInfo from info table and count of status from Schedule table.

I have used this query

select count(s.status), a.compleInfo, a.bank, a.number 
from (select in.compleInfo, cr.bank, cr.number 
      from criteis cr, info in  where cr.id=in.id ) a
left join schedule s on a.id = s.id group by a.id

It works fine on MYSQL Editor but when i run it on Hibernate i am getting below exception

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, [ select count(s.status), a.compleInfo, a.bank, a.number from (select in.compleInfo, cr.bank, cr.number from criteis cr, info in where cr.id=in.id ) a left join schedule s on a.id = s.id group by a.id]

i googled it and found Nhibernate HQL Subselect queries that hql does not support subqueries in the from clause.

Is there any other way to write the above query in Hibernate?

HQL allows you to walk the object tree but it's not SQL.

If you want to run SQL queries, you must use native SQL queries.