I am trying to execute the following query using hibernate criteria. But, it is not giving me correct results.
Query:
SELECT *
FROM TableA a
WHERE NOT EXISTS (SELECT *
FROM TableB b
WHERE b.col1 = a.col1
AND b.flag = 'Y');
Hibernate:
Criteria criteria = session.createCriteria(TableA.class, "a");
DetachedCriteria dc = DetachedCriteria.forClass(TableB.class, "b");
dc.add(Property.forName("b.col1").eqProperty("a.col1"));
dc.add(Restrictions.eq("b.flag","Y"));
dc.setProjection(Property.forName("b.col1"));
criteria.add(Subqueries.notExists(dc));
The query it is generating is-
select * from TableA a where not exists (select b.col1 from TableB b where **b.col1=b.col1** and b.flag='Y');
Here, I am not sure why it is comparing b.col1 with b.col1.
Can someone please help in fixing this issue. Thanks in advance.
-- Teja.