2
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
dc5
  • 12,341
  • 2
  • 35
  • 47
user2541372
  • 31
  • 3
  • Won't this always result in `answer = M` ? – Ashish Gaur Sep 01 '13 at 16:31
  • No, in "answer" i want to get either YES or NO separately for Male ("M") and Female ("F"). But Now I need only for Male so in subquery I have make a condition as answer="M". – user2541372 Sep 01 '13 at 16:58
  • You are selecting the answer back again in outer query. – Ashish Gaur Sep 01 '13 at 17:05
  • Yeah . Both inner query and outer query are there in same table with 2 different rows. In inner query I get subject name (Person name with gender as male) Now only for selected persons i need answer(either yes or No) which is another row. – user2541372 Sep 01 '13 at 17:18
  • 1
    Problems with the query: (1) `question like 'X'` is not the same as `question like 'X%'` and means exactly `question = 'X'`, (2) you have not stated that `test=1` in the outer query, so you can get results mixed up when several tests will be stored in the table. – mas.morozov Sep 02 '13 at 12:50

1 Answers1

2

  1. 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.

  2. 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.

Community
  • 1
  • 1
mas.morozov
  • 2,666
  • 1
  • 22
  • 22
  • I don't need SQL query , I need Equivalent Criteria query for my sql query. Note that my SQL query is working fine and There are no two tables there is only one table . On same table I written both inner and outer queries. Thanks for your response . Hope you will get correct answer to my question – user2541372 Sep 02 '13 at 07:26
  • 1
    Oh, how could I miss the `hibernate-criteria` tag :) I have completely rewritten my answer. And by the way, I think, your original SQL query has problems and may 'work fine' just because the lack of testing. – mas.morozov Sep 02 '13 at 12:47