Hi everybody
my question is as simple to write in SQL as I write below:

select parent.name, child.name, child.item_order
from parent_child parent, parent_child child
where parent.id = child.containerId
order by 3

then, how can i get the same result with HQL of, preferibly, with Criteria API. I tried a lot of things and i have read the chapted dedicated to associations with Criteria in the Hibernate book... and, obviously, i have found answers in this forum without sucess.

the table is so simple:
parent_child (id, parentid, name, item_order),
PK=id, FK= parentid->id (typical recursive relationship)
the order is important (item_order)

any suggestion, plizzzzzzzzzzz

thanx in advance and sorry whether i didnt look for deep enough.

w i l l y

Hibernate version: 3.2.6

Mapping documents: JPA

Name and version of the database you are using:PostgreSQL 8.1

try something like this

session.createCriteria(Parent.class)
.createAlias("pathToChild","alias")
.setProjection(Projections.projectionList()
.add(Projections.property("name"))
.add(Projections.property("alias.name"))
.add(Projections.property("alias.order")))
.addOrder(Order.asc("alias.order"))