Hello,
I need help in finding a way how I can create an hql query that would return me parent object only if none of the child object match a where clause.

e.g I have a parent->child relation between Person->Pets. Pets is a Set on Person. I want the hql query to be - return the person objects from db where the person object doesn't have any Pets that has the petName property value asDog.
Select * from Person left outer join Person.Pets pet where ???

I am not sure what the where could be here in hql to have pet.petName != Dog for any of the objects in my Set Pets.

from Person p where p.pets.size = 0

actually I can't do from Person p where p.pets.size = 0 because I need to check for the Set pets of person where none of the pet has the property petName = Dog - the set pets may contain objects, but none of them would be the one with petName = Dog.

Unfortunately, my collection here is a subclass of a baseclass and the property petName is on my baseclass - so, I can't apply hibernate filter here. And, I need to do this as part of query itself and return only the Person objects that match my criteria. Anyone has come across such query?

ok, not sure why I went w/ size ...

try "select p.person from Pet p where p.petName != 'foo'"

this doesn't work for me because if I do
select * from person p left outer join p.pets pet where pet.petName != 'dog', if I have the person "XXX" with set pets containing e.g 'dog' and 'cat', because one of the set object is not a dog (in this case, it is true because the set had 2 objects - and one of them has petName= 'cat' is not a dog), this select query returns me the person object "XXX" even though it does contain a pet with name 'dog'.

so, I am really not sure how I do such query with HQL. Any other suggestions? please?

look, I misinterprettted you at first but I simply did not suggest a join. i'll help you on this but I don't really know what you are asking? do you realize that last post is all one sentence?

select p
from Person p
left join p.pets q
where q.name != ?

Dennis,
Yes sorry for not explaining it right. In your second answer - you have select p.person from Pet p where p.petName != 'foo'. But that is not what I want. I want to do select query for a person and not pet. And the where clause I want is something that would get me the person ONLY if none of the pets for that person are with pet.petName = 'dog'.....

so, something like this for a select query(I am not writing the exact sql since I don't know what would go in where) :
select * from Person p where NOT (p.pets.petName = 'dog');

meaning return me the object pet ONLY if none of the pet I have in set pets is with petName = dog.

Did you read my post?

Actually, that should probably be an INNER JOIN, not a LEFT JOIN.

Does you database support subselects? If it does, than you should try to play with 'exists' query keyword.
This could be like this:


select * from Person p
where not exists(select pet from p.pets where p.petName = 'dog');

i.e. to be more correct in terms of HQL syntax, last should be read as follows: