3

I have a requirement to convert the below SQL query into a criteria query. There is just one table and note that it is not a case for self join.

SELECT atable.c1 AS ac1, btable.c1 as bc1 
  FROM (
       SELECT a.c1 
         FROM table_child a 
        WHERE a.c2='XXX') atable 
       LEFT OUTER JOIN (
           SELECT b.c1 
             FROM table_child b 
            WHERE b.c3 ='YYYYY') btable 
       ON atable.c1 = btable.c1

I am aware of how to use DetachedCriteria using org.hibernate.criterion.Subqueries to do things like IN(), NOT EXISTS(), etc. But the SubQueries class seems to be mainly for WHERE clause.

How do I use a SubQuery to perform a join as shown in the above SQL query. I need to do this using Hibernate Criteria Query.

Thanks.

mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
ksrini
  • 1,412
  • 2
  • 19
  • 33
  • I realize I can do this with a subselect. But can I have a subselect in the projection list in a criteria query? If yes, how? SQL equivalent would something like - select person.name, (select city.name from T_CITY city where city.code = person.city) as city_name from T_PERSON person – ksrini Feb 01 '11 at 09:58

0 Answers0