Hi,

I want to write a "select distinct" query in HQL.
my query has a lot of joins, so i have a lot of duplicates in the results -
and this cause a performace issue.

The idea of the query is as follows (see the query below):
I have two entities "A" and "B".
"A" has a reference to "B" (Many-to-Many).
i want all the objects of B.
i also want some of the data of B returning eagerly.

The problem is i get a lot of duplicates of "b"
in numbers i get 70,00 instead of 700.

i work above "sql server 2000",
and i understand there is problem using "select distinct" (in hibernate)
over this database - beacause the generated sql is invalid (i tried it and it didnt work).

i also tried "group by" on "b", but the generated sql is "group by" only on the "b.id" - which doesn't work.


the query is something as the following:
==========================
select b
from A a
inner join a.b b
left join fetch b.some_object1
left join fetch b.some_object2
left join fetch b.some_object2.some_object3
left join fetch b.some_object4
left join fetch b.some_object5
left join fetch b.some_object6
where a.some_field=true


thanks,
Eran.