I have tables that are structured something like this in Java:

```
class Owner {
    Long id; //primary key
    int age;
    List<Pet> pets; //one-to-many reference
    //...
}
class Pet {
    PetId id;
    Owner owner;
    //...
}
class PetId {
    BigDecimal ownerId;
    String type; //dog, cat, fish, archaeopteryx
}

```

and in the underlying SQL Server database:

```
Owner:
    id numeric(38,0) [pk]
    age numeric(38,0)
    //...
Pet:
    ownerId numeric(38,0) [pk]
    type varchar(30) [pk]
    //...

```

I'm trying to get a list of all the `Owners` of a given `age` that have `Pets` of both of two given `types`.

---

I have a query that works just fine in SQL Server:

```
select distinct o.id from Owner
inner join Pet pet1 on pet1.owner = o.id and pet1.type = @type1
inner join Pet pet2 on pet2.owner = o.id and pet2.type = @type2
where o.age = @age
order by o.id asc

```

I want to convert this into HQL. I could probably use a NativeQuery with this syntax, but I'd like to avoid that if at all possible.

---

SUB JOIN
--------

My initial attempt was a nearly direct transformation of the native SQL:

```
List<int> = em.createQuery("select distinct o.id from Owner o " +
" join o.pets pet1 " +
" join o.pets pet2 " +
" where o.age = :age" +
" and pet1.type = :type1 " +
" and pet2.type = :type2 " +
" order by o.id asc ")
.setParameter("age", age)
.setParameter("type1", type1)
.setParameter("type2", type2)
.getResultList();

```

My issue is that this produces the `org.hibernate.exception.SQLGrammarException`: "could not execute query".

---

CROSS JOIN
----------

I wondered if the same sub-data could not be joined multiple times and a cross join might be necessary:

```
List<int> = em.createQuery("select distinct o.id from Owner o, Pet pet1, Pet pet2 " +
" where o.age = :age" +
" and pet1.owner.id = o.id " +
" and pet2.owner.id = o.id " +
" and pet1.type = :type1 " +
" and pet2.type = :type2 " +
" order by o.id asc ")
.setParameter("age", age)
.setParameter("type1", type1)
.setParameter("type2", type2)
.getResultList();

```

But this produces the same error.

---

SUBQUERY
--------

I looked at the HQL documentation and found the subquery syntax:

```
List<int> = em.createQuery("select distinct o.id from Owner o " +
" where o.age = :age" +
" and o.id in (select pet1.owner.id " +
"              from Pets pet1 " +
"              where pet1.type = :type1 ) " +
" and o.id in (select pet2.owner.id " +
"              from Pets pet2 " +
"              where pet2.type = :type2 ) " +
" order by o.id asc ")
.setParameter("age", age)
.setParameter("type1", type1)
.setParameter("type2", type2)
.getResultList();

```

But that produces the same error yet again. What have I missed or done wrong in the queries, or this not possible using HQL?