title: Getting all children and subchildren from parent JPA
body: <p>I have a table TABLE that can have parents of the same type.
  In Java the child can reach the parent but the parent doesn't have a list of children.</p>
  
  <p>In MySQL I was able to create the following query that gives me the children and subchildren of the parent, but I'm unable to translate this into JPA Java.</p>
  
  <p>How can I translate this query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * 
  FROM TABLE AS T1 
  INNER JOIN 
    (SELECT id FROM TABLE WHERE TABLE.parentId = 966) AS T2 
    ON T2.id = T1.parentId OR T1.parentId = 966 
  GROUP BY T1.id
  </code></pre>
  
  <p>Into java language, using Criteria builder and Criteria Query, I already tried something like this:</p>
  
  <pre class="lang-java prettyprint-override"><code>CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery&lt;TABLE&gt; cq = cb.createQuery(TABLE.class);
      Root&lt;TABLE&gt; tABLE= cq.from(TABLE.class);
  
      Predicate result = cb.conjunction();
  
      Join&lt;TABLE, TABLE&gt; TABLEJoin = tABLE.join("parent", JoinType.INNER);
      result = cb.and(result, cb.equal(genericLocationJoin.get("parent"), location));
  em.createQuery(cq.select(tAble).where(result)).getResultList();
  </code></pre>
  
  <p>But this only gives me the direct children, it doesn't give me the subchildren.</p>
  
  <p>Thank you for your help.</p>
  
  <p>Entity:</p>
  
  <pre><code>@Entity
  @Table(name = "table")
  public final class TABLE {
  
      @Column(nullable = false, unique = true, length = 20)
      private String externalId;
  
      @Column(nullable = false, length = 50)
      private String name;
  
      @ManyToOne(fetch = FetchType.EAGER)
      @JoinColumn(name = "parentId", nullable = true)
      private TABLE parent;
  }
  </code></pre>