Hi! I'm new with Hibernate and HQL and I'm sure many of you can easily solve my problem, which seems quite challenging to me!

I'm trying to get Hibernate to return a tuple with:
1) an Order object

2) the sum of Bill.value:s, where the Bills are related to the Order in question through one or more OrderItems

3) the sum of Payment.value:s, where the Payments are related to the above Bills

The relationships are as follows:
Order <-- OrderItem --> Bill <-- Payment

where

<-- is a one-to-many-relationship (an Order may have many OrderItems, a Bill may have many Payments)

--> is a many-to-one-relationship (a Bill may have many OrderItems)


The specific problem I can't overcome is this: whatever I try, I always end up getting the value of each Bill or Payment multiplied by the number of OrderItems in an Order. OR the query is very slow. Below are a few examples:

My first try was quite simple:

**
select o,sum(b.value),sum(p.value) from Orders as o
join o.orderitems as i
join i.bill as b
join b.payments as p
where o.ordernumber = 2330
group by o
**

But the results are incorrect if there are multiple OrderItems in the order.

Using a few subqueries I managed to force the uniqueness of the objects being summed:

**
select o,sum(b.value),sum(p.value) from Orders as o, Bills as b, Payments as p
where o.ordernumber = 2330
and l in (select distinct r.lasku from Tilausrivi as r
where r.tilaus = t)
and m in (select distinct m from Maksu as m
inner join m.lasku as l
inner join l.tilausrivis as r
where r.tilaus = t)
group by t
**

The above query returns the correct answers, but takes an unbelievably long time (half a second, in comparison to the incorrect query which takes a few milliseconds)! If I query all the tuples for some o.client, executing that query takes some 5 seconds!

Please help! I probably have missed some technique which doesn't involve using such subqueries and "in"-keywords, as they seem to slow things down a lot!