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:
Code:
From ObjectA a where ? between a.date1 and a.date2 and ( (select c from a.cObjectList) in (select elements(b.cObjectList from ObjectB b where b.id =?) ) ....
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.