How to retrieve objects using criteria, detached criteria and subquery

Question

For four days now I´ve been trying to perform a query in my database. Heres the problem: I have a Group of Users Users can have birth date (it might be null) I want to list Groups that have no Users AND Groups that have Users in which all the users have 'null' for the birthdate I was able to get the Groups that have no Users easily (I did a left join), my problem is with the second part, getting groups where all users have null for the birthdate. So far this is the code I have: Criteria c = session.createCriteria(Group.class); c.createAlias("userCollection", "u", Criteria.LEFT_JOIN); c.add(Restrictions.or(Restrictions.isNull("userCollection"), Restrictions.isNull("u.birthDate"))); This works fine, but when a user in the group have a birthDate not null the group still appears in the query because others in the group have a null birthdate. I think I need to use not in, right? If anyone could help please, I tried a lot but I am getting a null pointer exception. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

The easiest way to do this is to exclude groups that have users with non-null birthDate, which takes care of both conditions simultaneously (groups with no users would not be on this list due to inner join). So, something like: DetachedCriteria excludedGroups = DetachedCriteria.forClass(Group.class); excludedGroups.createAlias("userCollection", "u"); excludedGroups.add(Restrictions.isNotNull("u.birthDate")); excludedGroups.setProjection(Projections.id()); Criteria c = session.createCriteria(Group.class); c.add(Subqueries.propertyNotIn("id", excludedGroups)); BTW, doing this via HQL would be even easier.