Select "answer"
from 'Details'
where "question" like 'child'
AND "subject" IN (select "subject"
from 'Details'
where "question" like 'child'
AND "answer" = "M" and "test" ="1");
Table Struture is :
Subject Test Survey Question answer
----------------------------------------------------
rahul 1 one childimmunization Yes
rahul 1 one childgender M
Jyothi 1 one childimmunization No
Jyothi 1 one childgender F
Chikku 1 one childimmunization No
Chikku 1 one childgender M
The most proper SQL-way to gather values from two or several rows into colums of single row is to join table with itself (one or more times). It is simpler and more convenient, than using subquery.
So, if you can create circular association from Deatils
to Details
in Hibernate - it would be the simplest solution. But I guess this is not possible, because Hibernate has known issue (and open task) with using same entity in join twice - details are here and here.
But according this answer you still can try to use Hibernate DetachedCriteria
as a subquery to make something very like your original SQL.
I will try to suggest this code, which was not tested though:
DetachedCriteria subjectsWithBoys = DetachedCriteria.forClass(Details.class)
.setProjection(Property.forName("subject"))
.add(Restrictions.eq("question", "childgender"))
.add(Restrictions.eq("answer", "M"))
.add(Restrictions.eq("test", "1"));
Criteria criteria = getSession().createCriteria(Details.class)
.setProjection(Property.forName("answer"))
.add(Property.forName("subject").in(subjectsWithBoys))
.add(Restrictions.eq("question", "childimmunization"))
.add(Restrictions.eq("test", "1"));
Hope you will make use of it easily and will not hit another open issue in Hibernate with such a trivial task.