[SQL] How to use subquery into "from" clause in Hibernate?

I have three tables: class, student, and teacher

table class
{
class_id(PK)
}

table student
{
student_id(PK)
class_id(PK+FK)
}

table teacher
{
teacher_id(PK)
class_id(PK+FK)
}


I have a query in SQL, which works fine.

SELECT data.class_id, count(data.class_id) AS count
FROM ((SELECT class_id FROM student)
union all
(SELECT class_id FROM teacher)) AS data
GROUP BY data.user_id
ORDER BY count desc


The query contains sub query in the from clause and union operation. I unable to convert it to the HQL.

Please, give me the efficient HQL query from the above SQL query.

Continue reading...