title: Hibernate : self join confusion?
body: <blockquote>
    <p>I have a <code>category</code> table.In which first 5 are main category and
    others are sub category.</p>
  </blockquote>
  
  <p><a href="https://i.stack.imgur.com/AiYQq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AiYQq.png" alt="enter image description here"></a></p>
  
  <p>I need to fetch the sub categories of first 5 main category so i have found the sql query</p>
  
  <pre><code>SELECT m.category_id,m.category_name  AS 'Parent',
        e.category_name AS 'Sub'
  FROM category e
  INNER JOIN category m ON m.category_id = e.parent_category_id
  ORDER BY Parent
  </code></pre>
  
  <p>The query is joining the same table itself.and am getting the result given below</p>
  
  <blockquote>
    <p><strong>Result</strong></p>
  </blockquote>
  
  <p><a href="https://i.stack.imgur.com/6xJL9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6xJL9.png" alt="enter image description here"></a></p>
  
  <blockquote>
    <p>How can i convert the SQL query to HQL and return the data like above image to user in
    standard json format ?</p>
    
    <p>FetchSubCategory</p>
  </blockquote>
  
  <pre><code>import java.io.Serializable;
  import java.util.Set;
  import javax.persistence.*;
  
  @Entity
  @Table(name = "category")
  public class FetchSubCategory implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = "category_id")
      private Integer categoryId;
  
      @Column(name = "category_name")
      private String categoryName;
  
      @ManyToOne(cascade = {CascadeType.ALL})
      @JoinColumn(name = "parent_category_id")
      private FetchSubCategory parent;
  
      @OneToMany(mappedBy = "parent")
      private Set&lt;FetchSubCategory&gt; subCategory;
  
      public Integer getCategoryId() {
          return categoryId;
      }
  
      public void setCategoryId(Integer categoryId) {
          this.categoryId = categoryId;
      }
  
      public String getCategoryName() {
          return categoryName;
      }
  
      public void setCategoryName(String categoryName) {
          this.categoryName = categoryName;
      }
  
      public FetchSubCategory getParent() {
          return parent;
      }
  
      public void setParent(FetchSubCategory parent) {
          this.parent = parent;
      }
  
      public Set&lt;FetchSubCategory&gt; getSubCategory() {
          return subCategory;
      }
  
      public void setSubCategory(Set&lt;FetchSubCategory&gt; subCategory) {
          this.subCategory = subCategory;
      }
  
  }
  </code></pre>
  
  <blockquote>
    <p>Method</p>
  </blockquote>
  
  <pre><code>public Set&lt;FetchSubCategory&gt; fetchSubCategory() throws SQLException, ClassNotFoundException, IOException {
          Set&lt;FetchSubCategory&gt; groupList = null;
          try {
              Session session = sessionFactory.getCurrentSession();
              Query query = session.createQuery("SELECT m.categoryName AS 'Parent', e.categoryName AS 'Sub' FROM FetchSubCategory e INNER JOIN FetchSubCategory m ORDER BY Parent");
  
              groupList = (Set&lt;FetchSubCategory&gt;) query.list();
          } catch (Exception e) {
              e.printStackTrace();
          }
  
          return groupList;
      }
  </code></pre>
  
  <p><strong><em>Can any one please correct my mistake and tell me how to fetch result like above image?</em></strong> </p>