title: selecting parent child from same table in mysql
body: <p>After searching a lot here, I could not find solution to my problem. So, I am posting this question.</p>
  
  <p>I have a Database Table which has the structure like this:</p>
  
  <pre><code>folder_id    folder_name    parent_id
  --------------------------------------
      1          Private          0
      2          Public           0
      3          Photos           0
      4          December         3
      5          Bday             4
      6          Celeb            5
  </code></pre>
  
  <p>In hierarchical form, it will be like a folder structure :</p>
  
  <pre><code>-- Private
  -- Public
  -- Photos
  ----- December
  -------- Bday
  ----------- Celeb
  </code></pre>
  
  <p>Now, I would like to select a Path upto a particular folder, like to <strong>Bday</strong> or <strong>Celeb</strong> folder.
  Thus, I want a MySQL query which will return me only the rows containing the folders between the path to a specific folder.</p>
  
  <p>For Example, If I want a path to <strong>Celeb</strong> folder, then the Query should return these rows only :</p>
  
  <pre><code>folder_id    folder_name    parent_id
  --------------------------------------
      3          Photos           0
      4          December         3
      5          Bday             4
      6          Celeb            5
  </code></pre>
  
  <p>Currently, I am stuck with this query and I am not able to make it work. The Query I am currently trying :</p>
  
  <pre><code>SELECT f.*
  FROM fd_folders f
  LEFT JOIN fd_folders p
  ON f.folder_id = p.parent_id
  WHERE f.folder_id &lt;=6
  ORDER BY f.folder_id;
  </code></pre>
  
  <p>But, the problem is that it is also returning the two other folders, i.e, <strong>Private</strong> and <strong>Public</strong>.</p>
  
  <p>Please help.</p>
  
  <p>Thank You</p>
  
  <p>Best Regards</p>