HQL, left join on the same table

Question

I search a way to do a left join with the same table with hql.

It's my query

  FROM Tvshow e
  LEFT JOIN Tvshow e1 ON e1.num = e.num
 WHERE e1.code = '024'
   AND e.code is not null
   AND e.code != '024'

Hibernate don't seem to like on operator.

Answer 1

Left joins in HQL are only possible if you have an association between two entities. Since your query imposes the joined entity to be non null, an inner join would do the same thing. An inner join, with the join syntax, is also possible only if you have an association between two entities. But you can do it by simply adding an equality test in the where clause:

select e from Tvshow e, Tvshow e1
where e.num = e1.num
and e1.code = '024'
and e.code is not null
and e.code != '024'