I have a similar probliem like this:

Ship stores containers, container holds boxes, boxes have a weight.

I now want to load those ships that have a total weight less than 100 tons.

In SQL it would be something like:

SELECT ship_id
FROM ship, container, box
WHERE ship.ship_id = (+)conatiner.ship_id
AND container.container_id = (+)box.container_id
GROUP BY ship.ship_id
HAVING SUM(box.weight) < 100

I tried several approaches but either I end up with the correct ships selected but only their id's, ie a List containing Integers (Root Object for Criteria was box.class). Or the call crashes cause it tries to select the weight directly from the ship object (Root Object for Criteria was ship.class)


I can't get it running, neither with HQL nor the Criteria API.


Plase, any Help welcome....

Just making a guess, but I think this might be the answer

select ship
from Ship ship
join ship.containers cont
join cont.boxes box
group by ship.id
having sum(box.weight) < 100