Hello all,

I'm trying to figure out how to write an inner join in HQL. I've checked the HQL doc which doesn't seem to help .. I'm doing an inner join on the same table (from one column to a different).

The SQL that I'm trying to translate to HQL is:

SELECT * FROM pets p INNER JOIN pets pp ON
p.petid = pp.similarid and pp.name = "rex"

Any advice on how to do this with HQL?

Thanks for any advice,
-d

Assuming that property SimilarPet is correctly mapped, then you'd useThe required mapping in Pet would be something like

Hmm.. Well .. the problem is that Pet doesn't have a property "SimilarPet" .. I just want to do an inner join on the same table.

A similar query is:

select * from Pet where petid in (select similarid from Pet where name = "Rex")

However this query take a very long time to complete (around 5 minutes). The "Pet" table has millions of rows.

Try:
SELECT p FROM Pets p, Pets pp WHERE p.petId = pp.similarId AND pp.name = "rex"

I've never tried this against the same class (table) but it works for arbitrary joins against different classes (tables).

That's it! Works great.. thanks!