Hibernate Criteria - Exclude records with same id but different attribute values

Question

Hi, I'm trying to find records where status = "a" for a Person but exclude records where the same person has another record with a status="b"

SELECT * FROM Person WHERE STATUS = 'a' AND Person_id NOT IN (SELECT Person_id FROM Person WHERE STATUS = 'b' AND Person_id IS NOT NULL)

Appreciate the help

Answer 1

You can write subqueries using Criteria API via DetachedCriteria:

DetachedCriteria dc = DetachedCriteria.forClass(Person.class);
dc.setProjection(Property.forName("id")); // select person_id
dc.add(Property.forName("status").eq("b")); // where status = 'b'

Criteria c = session.createCriteria(Person.class);
c.add(Property.forName("id").notIn(dc)); // where id not in (above subquery)
c.add(Property.forName("status").eq("a")); // and status = 'a'
c.list(); // TODO: handle results