Search code examples
javahibernatehql

Select category which is not a parent in HQL

I have a table category which has fields and values as shown below in MYSQL database.

id  name   parent    sort_order
1   Men    null       0
2   Women  null       1
3   shirt   1         0
4   salwar  2         1

I want to write a HQL Query for fetching all categories which is not parent of any other category. I cant slect by using parent is not null because i have more number of levels. Here shirts and salwar can be parent when another level comes. How can i do this?

Solution

  • Try with this hql

    select c1 from category c1 
where c1.id not in 
(select c2.parent_id from category c2 where c2.parent_id = c1.id)