Hibernate version:3.0
Name and version of the database you are using:MySQL

Hi All,

I have a simple mapping of a Category Table with a Product Table.
A Category Table can have multiple sub categories. The Product Table has a FK reference to a Category.

Category:
ID NAME PARENT_ID
------------------------
1 foo null
2 bar 1
3 acme 2
4 apple 3

Product:
ID NAME CATEGORY_ID
-----------------------------
1 foo 3
2 bar 4
3 test 2

I would like to issue a query in hibernate to retrieve all Products that belong to a particular category or children of that particular category. This would I beleive require some sort of sub tree query.

Select p from Product where P.categoryId in (select category and children category ids that have a ancestor category id of 2)

Any help would be greately appreciated.

Thanks in advance

Use CONNECT BY or SELECT WITH or RECURSIVE SELECT, depending what is available on your SQL system. Altnatively, search for "nested set sql" and have a look at the latest CaveatEmptor unit tests.

Hi Christian, I am using MySQL. I could not find a connect by clause in the same. Could you assist with an example? Thanks much.

Did you do the second part of my advice?