Greetings,

i have this scenario with hibernate 3.2.5 and i'd like your help.

i have a department , employee relationship where in mapping i have :

<class name="Department" table="department">
....
<set name="employee">
<key column="employee_id" />
<one-to-many class="Employee" />
</set>
....

and the employee
<class name="Employee" table="employee">
....
<many-to-one name="department" class="Department" column="department_id" />
....

I am trying to query where department name is 'test' and the address of the employee is 'test ad'. The result should be sorted by the department id and the employee name. After a lot of search i ended up with this query :

select distinct d from Department d inner join fetch d.employee e where d.name='test' and e.address.street='test ad' order by d.id, e.name asc

1) Without the distinct the result is cartesian so i get duplicated (and more) rows (although the various posts mentioned that distinct would not be necessary). Why is this happening ?
2) The sort does not work at all. I always get random results.

I appreciate your replies.

Regards