SQLGrammar exception, coulmn 'Id' not found when executing native query
<p>I have the following tables:</p>

<p><strong>stdqualificationmaster</strong></p>

<pre><code>QualificationId studentRegNo CourseName Percentage

1               1            A           91 
2               1            B           81
3               1            C           71
4               1            D           61
5               2            A           91
6               2            B           81
7               2            C           71
8               2            D           50
</code></pre>

<p><strong>testmaster:</strong></p>

<pre><code>TestId  studentRegNo testLevel percentage

1       1            1         91 
2       1            2         81
3       1            3         71
4       2            1         80
5       2            2         99
6       2            3         87
</code></pre>

<p>This is my <strong>PlacementDaoRdbms</strong> class which executes the native SQL query for the 2 tables above:</p>

<p><strong>PlacementDaoRdbms</strong></p>

<pre><code>package com.ms.avalon.master.dao.rdbms;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.SQLQuery;
import org.hibernate.Transaction;
import org.hibernate.criterion.DetachedCriteria;
import com.ms.avalon.master.beans.PlacementCaseBean;
import com.ms.avalon.master.dao.PlacementDao;
import com.ms.avalon.master.formbeans.PlacementFormBean;
import com.ms.avalon.master.pojos.studentpojos.StudentEduPojo;
import com.ms.avalon.master.pojos.studentpojos.StudentTestPojo;

public class PlacementDaoRdbms extends DBConnectionDao implements PlacementDao {

@Override
public List&lt;StudentEduPojo&gt; searchWithCases(PlacementCaseBean caseBean, PlacementFormBean placementBean) {

    System.out.println(session);
    System.out.println(hibernateTemplate);

    DetachedCriteria criteriaE = DetachedCriteria.forClass(StudentEduPojo.class, "qualTable");
    DetachedCriteria criteriaT = DetachedCriteria.forClass(StudentTestPojo.class, "testTable");

    String query = 
            "SELECT stdqualificationmaster.studentRegNo "
            + "FROM "
            + "(SELECT studentRegNo FROM stdqualificationmaster "
            + "WHERE (CourseName = 'High School' AND Percentage &gt;= 90) "
            + "OR (CourseName = 'Intermediate' AND Percentage &gt;= 80) "
            + "OR (CourseName = 'BCA' AND Percentage &gt;= 70) "
            + "OR (CourseName = 'MCA' AND Percentage &gt;= 60) "
            + "GROUP BY studentRegNo "
            + "HAVING COUNT(1) = 4 "
            + ") stdqualificationmaster JOIN "
            + "(SELECT studentRegNo FROM testmaster "
            + "WHERE (testLevel = '1' AND percentage &gt;= 90) "
            + "OR (testLevel = '2' AND percentage &gt;= 80) "
            + "OR (testLevel = '3' AND percentage &gt;= 70) "
            + "GROUP BY studentRegNo "
            + "HAVING COUNT(1) = 3 "
            + ") testmaster ON stdqualificationmaster.studentRegNo = testmaster.studentRegNo;";

    Transaction tx = session.beginTransaction();
    SQLQuery q = session.createSQLQuery(query)
    .addEntity(StudentEduPojo.class);

    List&lt;StudentEduPojo&gt; list = q.list();
    System.out.println(list.size());

    return null;
}

}
</code></pre>

<p>Alright, so basically the SQL query searches for studentRegNo which matches the minimum criteria in both the tables (has minimum qualification and test percentages). Once the query executes, following exception is thrown.</p>

<blockquote>
  <p>Hibernate: SELECT * FROM (SELECT studentRegNo FROM stdqualificationmaster WHERE (CourseName = 'High School' AND Percentage >= 90) OR (CourseName = 'Intermediate' AND Percentage >= 80) OR (CourseName = 'BCA' AND Percentage >= 70) OR (CourseName = 'MCA' AND Percentage >= 60) GROUP BY studentRegNo HAVING COUNT(1) = 4 ) stdqualificationmaster JOIN (SELECT studentRegNo FROM testmaster WHERE (testLevel = '1' AND percentage >= 90) OR (testLevel = '2' AND percentage >= 80) OR (testLevel = '3' AND percentage >= 70) GROUP BY studentRegNo HAVING COUNT(1) = 3 ) testmaster ON stdqualificationmaster.studentRegNo = testmaster.studentRegNo;</p>
  
  <p>May 12, 2016 3:37:50 PM org.apache.catalina.core.StandardWrapperValve
  invoke SEVERE: Servlet.service() for servlet [dispatcher] in context
  with path [/Avalon-1.0] threw exception [Request processing failed;
  nested exception is <strong>org.hibernate.exception.SQLGrammarException:
  could not execute query] with root cause java.sql.SQLException: Column
  'QualificationId' not found.</strong>    at
  com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)    at
  com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)     at
  com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)     at
  com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1136)  at
  com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2777)  at
  org.apache.commons.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:236)
    at org.hibernate.type.IntegerType.get(IntegerType.java:28)  at
  org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)    at
  org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)    at
  org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)     at
  org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:553)  at
  org.hibernate.loader.Loader.doQuery(Loader.java:689)  at
  org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
    at org.hibernate.loader.Loader.doList(Loader.java:2144)     at
  org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)    at
  org.hibernate.loader.Loader.list(Loader.java:2023)    at
  org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
    at
  org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
    at
  org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)  at
  com.ms.avalon.master.dao.rdbms.PlacementDaoRdbms.searchWithCases(PlacementDaoRdbms.java:107)
    at
  com.ms.avalon.master.business.TalentCaseHandler.searchForCases(TalentCaseHandler.java:63)
    at
  com.ms.avalon.master.service.impl.PlacementServiceImpl.searchForTalent(PlacementServiceImpl.java:31)
    at
  com.ms.avalon.master.controllers.PlacementController.searchForTalent(PlacementController.java:29)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  at
  sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)   at
  sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)   at
  java.lang.reflect.Method.invoke(Unknown Source)   at
  org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
    at
  org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:427)
    at
  org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:415)
    at
  org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:788)
    at
  org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:717)
    at
  org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
    at
  org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)     at
  javax.servlet.http.HttpServlet.service(HttpServlet.java:727)  at
  org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at
  org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at
  org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at
  org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at
  org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at
  com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:119)
    at
  com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:55)
    at
  org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at
  org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at
  org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at
  org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at
  org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
    at
  org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at
  org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at
  org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at
  org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at
  org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
    at
  org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
    at
  org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
    at
  org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at
  org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)</p>
</blockquote>

<p>Now, I am not sure why was it looking for the QualificationId column in the first place and even when it is, why is it not finding it?</p>