I have a customer table and a phone number table. A customer could have more than one phone number (home, mobile, work, evening etc..) and the Hibernate mapping files are set to bi-direcdtional 1 to many association.

For a given customer first name and the phone type (say "mobile"), I would like the hql to return a List of customers with the specific first name and the particular phone type associated with that customer.

i.e a list of customers where customer.name = "John" and for every customer returned the associated phone List should have only the mobile phone.

So when I use a query like this

List result = session.find("from Customer as cust join cust.phoneNumbers as phone where cust.firstName = ? and phone.type = ?", new Object[]{"John","Home"}, new Type[] {Hibernate.STRING, Hibernate.STRING});

The result contains a List of Object[2] where the first object is the customer and all its associated collections and the second object is phone number of the type specified in the where clause.

But I want the result to have list of customer objected with the associated phone list containing only those phones that match the where caluse.

Any help here would be appreciated. thanks.

Add "select cust" to the start of your query

You should then only get back the Customer objects. You can then traverse these objects to get your data.

When I do a select on customer, the customer object returned still has all phone numbers associated with it. But I only want the phone numbers that meet the criteria (a specific phone type)?

This is not possible, if you load an object, its associated collections are always in the state as stored in the Database. Use a collection filter to get the things you want.