0

I have a rather simple SQL query where I need to get data from a Parent table based on a query on another Child table. For example:

Parent table:

ID NAME
-- ----
22 Frederick
26 James
39 Robert

Child table:

PARENT_ID NAME
--------- ----
22        Joey
22        Cindy
26        Robbie
39        Joe

So I need an SQL query like this that would get the names of any parents who have a child with a name like Joe - i.e., Frederick and Robert.

select NAME from PARENTS where ID in (
  select PARENT_ID from CHILDREN where NAME like '%Joe%'
);

I'm trying something like this:

Subquery<Child> childSubquery = criteriaQuery.subquery(Child.class);
Root<Child> childRoot = childSubquery.from(Child.class);
Predicate likeChild = criteriaBuilder.like(childRoot.get(Child_.name), '%Joe%');

This would get me the children with names like Joe.

So how do I then get the parents of those children?

Based on this question, I tried like this: 

CriteriaQuery<Parent> parentQuery = criteriaBuilder.createQuery(Parent.class);
Root<Parent> parentRoot = parentQuery.from(Parent.class);
parentQuery.select(parentRoot);
parentQuery.where(likeChild);
parentQuery.getResultList() <--- this method doesn't exist in CriteriaQuery!!
Oscar Bravo
  • 250
  • 4
  • 11
  • 1
    https://stackoverflow.com/questions/42530677/jpa-criteria-builder-in-clause-query/42531461#42531461 – Maciej Kowalski Feb 04 '19 at 12:14
  • @MaciejKowalski Thanks for the link.. I actually looked at that answer, but I couldn't see how to match them up. For example, in that answer, the parentList is simply hard-coded as an array. In my case, the parentList is a Predicate from another query. – Oscar Bravo Feb 04 '19 at 13:05

0 Answers0