0

I have 2 database tables "parent" and "child". A parent may have many children.

I have a very complex database query and use JPA CriteriaBuilder to build my query.

I try to fetch all parents which have children that match specific criterias. For example any child must have the field "value1 = 19" and any other child must have "value1 = 29".

The sql query down below is working. How can I transform it into JPA using CriteriaBuilder?

select * from parent
where id in (
    select parent_id
    from child
    where value1 = 19 or value1 = 29
    group by parent_id
    having count(*) > 1
);

This is where I would start:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Parent> query = builder.createQuery(Parent.class);
Root<Parent> root = query.from(Parent.class);

The main problem is that I dont know how to do IN ( SELECT ...

azro
  • 47,041
  • 7
  • 30
  • 65
Bobin
  • 153
  • 2
  • 11
  • [The answer to your question should look something like this](https://stackoverflow.com/a/4668015/8223478) – Sumedh Jul 27 '21 at 17:17

0 Answers0