title: MySQL query or stored procedure that calls itself recursively and returns all nodes for selected parent
body: <pre><code>id | parent_id | name
  -------------------------
  1  | null      | World
  2  | 1         | Sri Lanka
  3  | 1         | America
  4  | 2         | South Province
  5  | 2         | Western Province
  6  | 4         | Galle
  7  | 6         | Wakwella
  8  | 3         | New York
  </code></pre>
  
  <p>I need a  MySQL query or stored procedure that calls itself recursively and returns all nodes,child nodes and leaf nodes for selected "id" .</p>
  
  <p>As a example:</p>
  
  <p>When i want to select all child of id=2
  Result should be,</p>
  
  <pre><code>South Province
  Western Province
  Galle
  Wakwella
  </code></pre>
  
  <p>When i want to select all child of id=3
  Result should be,</p>
  
  <pre><code>New York
  </code></pre>