title: Hibernate Criteria Query for multiple columns with IN clause and a subselect
body: <p>I have a question very similar to <a href="https://stackoverflow.com/questions/12411894/hibernate-criteria-query-for-multiple-columns-in-in-clause">this question</a>.</p>
  
  <p>I am selecting all data from table1 for all matching unique combinations of field3 and field4 from table2.</p>
  
  <p>Here is my stripped down SQL:</p>
  
  <pre><code>select *
  from table1 as t1
  where (t1.field1, t1.field2) in (select distinct field3, field4
                                   from table2 as t2
                                   where t2.id=12345);
  </code></pre>
  
  <p>I need to translate my SQL to Hibernate Criteria.  I have my entity objects mapping correctly to the tables and transform the response into the correct result entity, but I cannot get my where clause translated correctly.</p>
  
  <p><strong>What I Have</strong></p>
  
  <pre><code>Criteria criteria = getSession().createCriteria(Table1.class);
  
  DetachedCriteria subquery = DetachedCriteria.forClass(Table2.class);
  ProjectionList projectionList = Projections.projectionList();
  projectionList.add(Projections.property("field3"), "field3");
  projectionList.add(Projections.property("field4"), "field4");
  subquery.setProjection(Projections.distinct(projectionList));
  subquery.add(Restrictions.eq("id", 12345));
  </code></pre>
  
  <p>I want my where clause to be something like:</p>
  
  <pre><code>criteria.add(Subqueries.in("field1, field2", subquery));
  </code></pre>
  
  <p>But that is not allowed by Hibernate.</p>
  
  <p>I have tried rolling out the where clause to have two subqueries and checking both field1 and field2 against the results, but it seems like the subqueries will always have to return multiple columns.  I did this using group by but Hibernate will automatically add the columns in the group by to the projection list and I cannot find a way to remove them.</p>
  
  <p>Here is the same query using group by:</p>
  
  <pre><code>select *
  from table1 as t1
  where t1.field1 in (select field3
                      from table2 as t2
                      where t2.id=12345
                      group by field3, field4)
    and t1.field2 in (select field4
                      from table2 as t2
                      where t2.id=12345
                      group by field3, field4);
  </code></pre>
  
  <p>Is it possible to do my where clause using Hibernate Criteria?</p>
  
  <p>If it is not possible using Hibernate Criteria, is it possible to do my where clause using HQL?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>@Larry.Z answers my question by using HQL.</p>
  
  <p>I was able to solve my problem with Hibernate Criteria, but I had to modify the query to:</p>
  
  <pre><code>select *
  from table1 as t1
  where exists (select 1
                table2 as t2
                where t2.id=12345
                  and t2.field3=t1.field1
                  and t2.field4=t1.field2);
  </code></pre>
  
  <p>Translated to Hibernate Criteria:</p>
  
  <pre><code>Criteria criteria = getSession().createCriteria(Table1.class, "t1");
  
  DetachedCriteria subquery = DetachedCriteria.forClass(Table2.class, "t2");
  subquery.add(Restrictions.eq("t2.id", 12345));
  subquery.add(Restrictions.eqProperty("t2.field3", "t1.field1"));
  subquery.add(Restrictions.eqProperty("t2.field4", "t1.field2"));
  subquery.setProjection(Projections.property("t2.id")); // select the ID rather than 1
  </code></pre>
  
  <p>I'm still curious if it is possible to write Hibernate Criteria using my original SQL.</p>