How to I query sql for a Object in a Table with a single sub-object based on a field?

Question

What I have right now is:

Getting a List<Town> with all neighborhoods & etc:

(Town) em.createQuery(from Town towns"
        + " left join fetch towns.neighborhoods neighborhood"
        + " left join fetch neighborhood.population population"
        + " left join fetch population.age age"
        + " where age.value = :ageValue")
       .setParameter("ageValue", ageValue)

I want to be getting a List<Town> with only one neighborhood which is the first at neighborhood.creationDate.

How do I do that ?

I don't want to get them all and remove it after.

Thank you.

Answer 1

I suggest to add a subquery in the where clausule, something like this:

where age.value = :ageValue 
    AND neighborhood.creationDate = 
         (Select max(nh.creationDate) From Neighborhood nh 
               where nh.townsId = towns.townsId)