title: Join two tables HQL query
body: <p>How can i join two tables using HQL?</p>
  
  <p>At first, here is my SQL create query for two tables:</p>
  
  <pre><code>CREATE TABLE `subject` (
      `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(50) NOT NULL,
      PRIMARY KEY (`id`)
  )
  
  CREATE TABLE `employee` (
      `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `subject_id` INT(11) UNSIGNED NOT NULL,
      `surname` VARCHAR(50) NOT NULL,
      PRIMARY KEY (`id`),
      INDEX `FK_employee_subject` (`subject_id`),
      CONSTRAINT `FK_employee_subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
  )
  </code></pre>
  
  <p>I'm using Netbeans and here is my generated entities.</p>
  
  <p>Subject entity:</p>
  
  <pre><code>@Entity
  @Table(name = "subject", catalog = "university")
  public class Subject implements java.io.Serializable {
  
      private Integer id;
      private String name;
      private Set&lt;Employee&gt; employees = new HashSet&lt;Employee&gt;(0);
      private Set&lt;Report&gt; reports = new HashSet&lt;Report&gt;(0);
  
      public Subject() {
      }
  
      public Subject(String name) {
          this.name = name;
      }
  
      public Subject(String name, Set&lt;Employee&gt; employees, Set&lt;Report&gt; reports) {
          this.name = name;
          this.employees = employees;
          this.reports = reports;
      }
  
      @Id
      @GeneratedValue(strategy = IDENTITY)
  
      @Column(name = "id", unique = true, nullable = false)
      public Integer getId() {
          return this.id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      @Column(name = "name", nullable = false, length = 50)
      public String getName() {
          return this.name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      @OneToMany(fetch = FetchType.LAZY, mappedBy = "subject")
      public Set&lt;Employee&gt; getEmployees() {
          return this.employees;
      }
  
      public void setEmployees(Set&lt;Employee&gt; employees) {
          this.employees = employees;
      }
  
      @OneToMany(fetch = FetchType.LAZY, mappedBy = "subject")
      public Set&lt;Report&gt; getReports() {
          return this.reports;
      }
  
      public void setReports(Set&lt;Report&gt; reports) {
          this.reports = reports;
      }
  
  }
  </code></pre>
  
  <p>Employee entity:</p>
  
  <pre><code>@Entity
  @Table(name = "employee", catalog = "university")
  public class Employee implements java.io.Serializable {
  
      private Integer id;
      private Subject subject;
      private String surname;
      private Set&lt;Report&gt; reports = new HashSet&lt;Report&gt;(0);
  
      public Employee() {
      }
  
      public Employee(Subject subject, String surname) {
          this.subject = subject;
          this.surname = surname;
      }
  
      public Employee(Subject subject, String surname, Set&lt;Report&gt; reports) {
          this.subject = subject;
          this.surname = surname;
          this.reports = reports;
      }
  
      @Id
      @GeneratedValue(strategy = IDENTITY)
  
      @Column(name = "id", unique = true, nullable = false)
      public Integer getId() {
          return this.id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = "subject_id", nullable = false)
      public Subject getSubject() {
          return this.subject;
      }
  
      public void setSubject(Subject subject) {
          this.subject = subject;
      }
  
      @Column(name = "surname", nullable = false, length = 50)
      public String getSurname() {
          return this.surname;
      }
  
      public void setSurname(String surname) {
          this.surname = surname;
      }
  
      @OneToMany(fetch = FetchType.LAZY, mappedBy = "employee")
      public Set&lt;Report&gt; getReports() {
          return this.reports;
      }
  
      public void setReports(Set&lt;Report&gt; reports) {
          this.reports = reports;
      }
  
  }
  </code></pre>
  
  <p>I've tried to use query like this, but it doesn't work:</p>
  
  <pre><code>select employee.id, employee.surname, subject.name from Employee employee, Subject subject where employee.subject_id=subject.id
  </code></pre>
  
  <p>Here is my stacktrace, after using suggested query</p>
  
  <pre><code>org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: by near line 1, column 102 [select employee.id, employee.surname, subject.name from by.bsuir.yegoretsky.model.Employee employee, by.bsuir.yegoretsky.model.Subject subject where employee.subject_id=subject.id]
      at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
      at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
      at org.hibernate.engine.query.spi.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:126)
      at org.hibernate.engine.query.spi.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:88)
      at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190)
      at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
      at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
      at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1796)
  </code></pre>
  
  <p>And the screenshoot: <a href="https://i.stack.imgur.com/wtazq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wtazq.png" alt="error"></a></p>