Hi,

I have two objects called product and order. Order has foreign key (one to many) relationship with product table. Order object has a field/column called status. I want to find out a unique list of products which has got order status set to 'Dispatched'. In other words status field is in the order table, so i want to link order and product tables on product id and find a unique list of products where order status is 'Dispatched'.

Any help is greatly appreciated.

I'm going to assume it is actually a many-to-many relationship with Product since otherwise a given product could only be sold to one customer on one order. That being said you ask for a pretty simple HQL query:

"SELECT DISTINCT p FROM Order o INNER JOIN o.Products p WHERE o.Status = :status"

and then set your status parameter to 'Dispatched'.

If you mapped it with a one to many and a many to one mapping you'll just have to include the INNER JOIN to your intermediate object.

Hope that helps.