HQL query related to many-to-many relationship

[Shanika Weerapperuma]

Hibernate version: 2.1.7c

I'm having a problem in getting the proper HQL for the follwong senario:

Tables : ITEM (ItemID, name) 
ORDER (OrderID, Description) 
ITEM_ORDER (OrderID,ItemID) 

I have done the many-to-many relationship mapping. (In this, the ITEM has a set of Orders and The Orders has a set of Items)
ITEM is defined as the inverse end. The records for ITEM_ORDER table gets inserted when calling the session.save(order) and works fine.


Now, how to write the HQL to find out all the Orders objects that contains Item "ITM_01" ? In other words, i need to get all the Orders objects, that has Item "ITM_01" in their Item set.

please help.

[Shanika Weerapperuma]

oh, i found it and so impressed to see how simple it is :

select order from ORDER as order,ITEM as item where item.itemID like 'ITM_01' and item in elements(order.items)