title: SQL Error: 1054, SQLState: 42S22
body: <p>Hello Everyone I'm trying to retrive some data from my database but I'm getting</p>
  
  <pre><code>com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'subject_id' in 'where clause'
  </code></pre>
  
  <p>It works if I retrive some other column of same table. I'm using spring and hibernate</p>
  
  <p>Here is my controller code</p>
  
  <pre><code>@RequestMapping(value = "/getAnyQuestion", method = RequestMethod.GET)
  public @ResponseBody
  String getAnyQuestion(
          @RequestParam(value = "subId", required = true) Long subId,
          @RequestParam(value = "questionType", required = true) String questionType) {
  
      Question questionList = questionService.getAnyQuestion(subId,questionType);
  
      if (questionList != null) {
          questionId = questionList.getId();
          return questionList.getQuestionText();
      }
  
      return "";
  }
  </code></pre>
  
  <p>Here is my Service</p>
  
  <pre><code> public Question getAnyQuestion(Long subId,String questionType) {             
      String query = "subject_id = " + subId +"  and "+ "questionType='" + questionType +"' and 1=1 order by rand()";       
  
      List&lt;Question&gt; questionList = hibernateTemplate.findByCriteria(DetachedCriteria.forClass(Question.class).add(Restrictions.sqlRestriction(query)),0,1);
      if (questionList.isEmpty()) {
          return null;
      }
      return questionList.get(0);
  }
  </code></pre>
  
  <p>and here is my domain</p>
  
  <pre><code>@Entity
  </code></pre>
  
  <p>@Table(name = "question")
  public class Question {</p>
  
  <pre><code>@Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;
  @Column(length = 50)
  private String questionType;
  @Column(length = 5000)
  private String questionText;
  
  @ManyToOne
  private Syllabus syllabus;
  @ManyToOne
  private Subject subject;
  @ManyToOne
  private Roll roll;
  
  public Long getId() {
      return id;
  }
  
  public void setId(Long id) {
      this.id = id;
  }
  
  
  public String getQuestionText() {
      return questionText;
  }
  
  public void setQuestionText(String questionText) {
      this.questionText = questionText;
  }
  
  public String getQuestionType() {
      return questionType;
  }
  
  public void setQuestionType(String questionType) {
      this.questionType = questionType;
  }
  
  public Syllabus getSyllabus() {
      return syllabus;
  }
  
  public void setSyllabus(Syllabus syllabus) {
      this.syllabus = syllabus;
  }
  
  
  public Subject getSubject() {
      return subject;
  }
  
  public void setSubject(Subject subject) {
      this.subject = subject;
  }
  
  public Roll getRoll() {
      return roll;
  }
  
  public void setRoll(Roll roll) {
      this.roll = roll;
  }
  </code></pre>
  
  <p>}</p>
  
  <p>Ful StackTrace is</p>
  
  <pre><code>    21296 [http-bio-8080-exec-10] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1052, SQLState: 23000
  21296 [http-bio-8080-exec-10] ERROR org.hibernate.util.JDBCExceptionReporter - Column 'subject_id' in where clause is ambiguous
  Jun 26, 2013 7:30:12 PM org.apache.catalina.core.StandardWrapperValve invoke
  SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/Trainning] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute query; SQL [select this_.id as id3_4_, this_.questionText as question2_3_4_, this_.questionType as question3_3_4_, this_.roll_id as roll4_3_4_, this_.subject_id as subject5_3_4_, this_.syllabus_id as syllabus6_3_4_, roll2_.id as id4_0_, roll2_.contact as contact4_0_, roll2_.rollno as rollno4_0_, roll2_.studentName as studentN4_4_0_, subject3_.id as id0_1_, subject3_.course as course0_1_, subject3_.sem as sem0_1_, subject3_.subjectName as subjectN4_0_1_, subject3_.syllabuspath as syllabus5_0_1_, syllabus4_.id as id6_2_, syllabus4_.chapterName as chapterN2_6_2_, syllabus4_.subject_id as subject3_6_2_, subject5_.id as id0_3_, subject5_.course as course0_3_, subject5_.sem as sem0_3_, subject5_.subjectName as subjectN4_0_3_, subject5_.syllabuspath as syllabus5_0_3_ from question this_ left outer join roll roll2_ on this_.roll_id=roll2_.id left outer join subject subject3_ on this_.subject_id=subject3_.id left outer join syllabus syllabus4_ on this_.syllabus_id=syllabus4_.id left outer join subject subject5_ on syllabus4_.subject_id=subject5_.id where subject_id = 1  and questionType='University' and 1=1 order by rand()]; Column 'subject_id' in where clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'subject_id' in where clause is ambiguous] with root cause
  com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'subject_id' in where clause is ambiguous
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.Util.getInstance(Util.java:384)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
      at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
      at org.hibernate.loader.Loader.doQuery(Loader.java:697)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
      at org.hibernate.loader.Loader.doList(Loader.java:2232)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
      at org.hibernate.loader.Loader.list(Loader.java:2124)
      at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1597)
      at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
      at org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:966)
      at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
      at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:956)
      at org.Trainning.service.question.QuestionServiceImpl.getAnyQuestion(QuestionServiceImpl.java:91)
      at org.Trainning.controller.activities.ActivityController.getAnyQuestion(ActivityController.java:749)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:601)
      at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
      at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
      at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:647)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:603)
      at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:950)
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:859)
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:883)
      at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:781)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
      at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
      at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
      at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539)
      at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
      at java.lang.Thread.run(Thread.java:722)
  </code></pre>
  
  <p>What I'm doing wrong please help me it works fine if i retrive syllabus data but give above error for subject</p>
  
  <p>Here is the query generated by hibernate:</p>
  
  <pre><code>Hibernate: select this_.id as id3_4_, this_.questionText as question2_3_4_, this_.questionType as question3_3_4_, this_.roll_id as roll4_3_4_, this_.subject_id as subject5_3_4_, this_.syllabus_id as syllabus6_3_4_, roll2_.id as id4_0_, roll2_.contact as contact4_0_, roll2_.rollno as rollno4_0_, roll2_.studentName as studentN4_4_0_, subject3_.id as id0_1_, subject3_.course as course0_1_, subject3_.sem as sem0_1_, subject3_.subjectName as subjectN4_0_1_, subject3_.syllabuspath as syllabus5_0_1_, syllabus4_.id as id6_2_, syllabus4_.chapterName as chapterN2_6_2_, syllabus4_.subject_id as subject3_6_2_, subject5_.id as id0_3_, subject5_.course as course0_3_, subject5_.sem as sem0_3_, subject5_.subjectName as subjectN4_0_3_, subject5_.syllabuspath as syllabus5_0_3_ from question this_ left outer join roll roll2_ on this_.roll_id=roll2_.id left outer join subject subject3_ on this_.subject_id=subject3_.id left outer join syllabus syllabus4_ on this_.syllabus_id=syllabus4_.id left outer join subject subject5_ on syllabus4_.subject_id=subject5_.id where subject_id = 1  and questionType='University' and 1=1 order by rand() limit ?
  </code></pre>
  
  <p>As suggested by Yak here is my subject domain</p>
  
  <pre><code>    @Entity
  @Table(name = "subject")
  public class Subject {
  
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
      @Column(length=80)
      private Double course;
      @Column(length=60)
      private String subjectName;
      @Column(length=10)
      private Double sem;
      @Column(length=300)
      private String syllabuspath;
  
      public Long getId() {
          return id;
      }
  
      public void setId(Long id) {
          this.id = id;
      }
  
      public Double getCourse() {
          return course;
      }
  
      public void setCourse(Double course) {
          this.course = course;
      }
  
      public Double getSem() {
          return sem;
      }
  
      public void setSem(Double sem) {
          this.sem = sem;
      }
  
      public String getSubjectName() {
          return subjectName;
      }
  
      public void setSubjectName(String subjectName) {
          this.subjectName = subjectName;
      }
  
      public String getSyllabuspath() {
          return syllabuspath;
      }
  
      public void setSyllabuspath(String syllabuspath) {
          this.syllabuspath = syllabuspath;
      }
  
  
  }
  </code></pre>
  
  <p>This is what desc gave me</p>
  
  <pre><code>| Field        | Type          | Null | Key | Default | Extra          |
  +--------------+---------------+------+-----+---------+----------------+
  | id           | bigint(20)    | NO   | PRI | NULL    | auto_increment |
  | questionText | varchar(5000) | YES  |     | NULL    |                |
  | questionType | varchar(50)   | YES  |     | NULL    |                |
  | roll_id      | bigint(20)    | YES  | MUL | NULL    |                |
  | subject_id   | bigint(20)    | YES  | MUL | NULL    |                |
  | syllabus_id  | bigint(20)    | YES  | MUL | NULL    |                |
  +--------------+---------------+------+-----+---------+----------------+
  </code></pre>