title: Stop recursive incestuous child parent relationship in mysql
body: <p>I am programming in PHP / MySQL / Javascript.
  I have a list of parts which we want to link in a child / parent relationship with no limit on the amount of tiers.</p>
  
  <p>When I am picking from a list of parts to add a child to a parent I limit the list of parts to exclude the parent itself, and any parts which are already children of that parent.</p>
  
  <p>What I have discovered is that I also want to exclude the grandparents of the parent as otherwise we can get an incestuous relationship, which when I display the tree of parts will create an infinite loop.</p>
  
  <p>Not only that but I cannot allow the child part to be a great grandparent of the parent or great great grandparent e.t.c.</p>
  
  <p>Here is the SQL statement I use currently which I think could also be improved by using <code>LEFT JOIN</code> but I am not skillful enough with SQL at this point.</p>
  
  <pre><code>SELECT * 
  FROM sch_part_general 
  WHERE (sch_part_general.part_id &lt;&gt; $parentId) 
  AND (sch_part_general.part_id NOT IN 
    (SELECT part_id FROM sch_part_mapping WHERE parent_id = $parentId)
  )
  </code></pre>
  
  <p>sch_part_general is a multi column table with all the parts, with part_id as the primary key.
  sch_part_mapping is a two column mapping table with part_id (child) || parent_id (parent).</p>
  
  <p>Could someone point me in the right direction with the SQL query? I am not keen on using a while loop to create the SQL statement as I think this will be quite inefficient but it is the only way I have considered might work so far.</p>