Search code examples
sqlhibernatehql

Convert left join SQL query to HQL

I have the following SQL query and wonder how to convert it to HQL. How would be the syntax?

select pr.PROD_ID, pr.ORG_UNIT_ID, pr.QTY, pr.ID from PROD_ST pr
left join
(select * from ORG_UNIT where code like '25%') org
on org.id = pr.ORG_UNIT_ID
where org.id = pr.ORG_UNIT_ID
Solution

  • At the first, your Sql LEFT JOIN, really is a INNER JOIN, because in the WHERE clause you have put:

    org.id = pr.ORG_UNIT_ID

    The second, in your SELECT field list you show only pr fields, so you can convert your LEFT JOIN with the EXISTS function like this (I'm supposing you have for each table an entity with same name):

    SELECT pr.PROD_ID, pr.ORG_UNIT_ID, pr.QTY, pr.ID from PROD_ST pr
WHERE EXISTS(
    SELECT 'ORG_UNIT'
    FROM ORG_UNIT org
    WHERE org.code like '25%'
    AND org.id = pr.ORG_UNIT_ID
)