Hi,

I have two objects, say ObjectA and ObjectB that both have a list of objects C. I need to display any ObjectA objects where it's list of C objects intersects with ObjectB's list of C objects.

My query looks something like this:

This works, except for the case where ObjectA has more than one value in it's cObjectList, I then get an Oracle "subselect returned more than one value exception". I could do something like (select 1 c from...) in the first subselect , but wouldn't that not always return accurate results? Is it possible in HQL do an Any, Exists type function where the first subselect returns multiple values?

I have searched the forum thoroughly I have read the docs, specifically section 14.9 on HQL expressions, but have not had much luck in getting an answer. Does anyone have any examples of how to go about doing this sort of query? I am using Hibernate 3.2, Annotations and Oracle 10g.

Thank you.

... subselect subselect returned more than one value exception would be a normal behaviour. Althoug not the necesary optimal performance, you can try that in actual java

... I see no way of intersecting THE WHOLE collection inside queries. Hibernate allows certan java api functionality inside HQL, would the .containsAll() work inside HQL ? you can try it ;)

Thanks but I found the solution I did
This did the trick.