title: SQL : loop through same table
body: <p>I have a table "projectdetails" in which <code>parent_id</code> is the foreign key of column <code>project_id</code> in the same table..</p>
  
  <p>From this below records I want only those rows whose <code>parent_id</code> does rows, does not have 'recycle_bin` value as 1 and also should display record for creater_id = 7923 ; </p>
  
  <p>I have record like this:</p>
  
  <pre><code>mysql&gt; SELECT *FROM  projectdetails;
  +------------+-----------------+-----------+-------------+------------+
  | project_id | project_name    | parent_id | recycle_bin | creater_id |
  +------------+-----------------+-----------+-------------+------------+
  |          0 | -               |         0 |           0 |       7898 |
  |        100 | Country         |         0 |           2 |       7923 |
  |        101 | animal          |         0 |           1 |       7923 |
  |        102 | India           |       100 |           2 |       7923 |
  |        103 | pakistan        |       100 |           2 |       7923 |
  |        104 | cow             |       101 |           1 |       7923 |
  |        105 | elephant        |       101 |           1 |       7923 |
  |        109 | black elephent  |       105 |           1 |       7923 |
  |        110 | white elephent  |       105 |           2 |       7923 |
  |        111 | wild black elep |       109 |           1 |       7923 |
  |        112 | simple blak elp |       109 |           1 |       7923 |
  |        113 | lion            |       105 |           1 |       7923 |
  |        114 | red lion        |       113 |           1 |       7923 |
  |        115 | black lion      |       113 |           1 |       7923 |
  |        116 | girls           |         0 |           1 |       7923 |
  |        117 | good girls      |       116 |           1 |       7923 |
  |        118 | funky girls     |       116 |           1 |       7923 | 
  +------------+-----------------+-----------+-------------+------------+
  7 rows in set (0.00 sec)
  </code></pre>
  
  <p>Expected output:</p>
  
  <pre><code>+------------+----------------+-----------+-------------+------------+
  | project_id | project_name   | parent_id | recycle_bin | creater_id |
  +------------+----------------+-----------+-------------+------------+ 
  |        100 | Country        |         0 |           2 |       7923 | 
  |        110 | white elephent |       105 |           2 |       7923 |
  +------------+----------------+-----------+-------------+------------+
  </code></pre>
  
  <p><strong>Note:</strong> This is tree structure table, here on each child id user can insert many other records.. i.e same like tree structure. So  please answer relevant to this note.</p>
  
  <p>If you could not get what i asked for, then please write comment, I will try to explain you...</p>
  
  <p>UPDATE</p>
  
  <p>So basically, when query reads a row to decide whether it is applicable to show or not,</p>
  
  <p>First query flow: say for country.</p>
  
  <p>First look for a row say INDIA, &amp; then next see its parent_id, if it have parent_id, then go to that parent_id(now this is a project_id=100), so next again see whether it has parent or not, if not then see column recycle_bin .. if 1 then show this result or else ignore. </p>