title: Using the results of one query in another
body: <p>We are using a hybrid of the nested sets model to allow a child to have multiple parents. It is an expansion on the nested set model described in Mike Hillyers blog: <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow">http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/</a> - this might help explain some of the column names.</p>
  
  <p>I am fairly new to SQL but I have investigated joins, subqueries etc. and nothing seems to offer me the result I need. I expect the answer is fairly simple though and will almost definitely be a <code>LEFT JOIN</code> but I can't put my finger on it.</p>
  
  <p>I have a simple table called 'nested_parts' with 5 columns: 'tree_id', 'part_id', 'lft', 'rgt' and 'mapping'.</p>
  
  <p>The following query returns the values I want to compare in the second query.</p>
  
  <pre><code>SELECT * FROM nested_parts WHERE part_id = 125
  </code></pre>
  
  <p>Specifically, I now know which tree_id's that part#125 exists in and the lft and rgt values for each tree_id.</p>
  
  <p>I now need to know all the part_id's which are in the array of tree_id results I pulled earlier.</p>
  
  <p>I used this a subquery for this:</p>
  
  <pre><code>SELECT * FROM nested_parts
  WHERE tree_id = ANY (SELECT tree_id AS tbl FROM nested_parts WHERE part_id = 125)
  </code></pre>
  
  <p>Now I need to know which part_id's have lft and rgt values that are not between the lft and rgt values of part#125 within the scope of each tree_id. This would be easy if I had two tables to compare still, or could use virtual tables but this doesn't seem possible here without a loop.</p>
  
  <p>Any help gratefully received but please appreciate that I am stupid not lazy, and although I have read plenty about UNIONS, JOINS, HAVING, WHERE, SELECT(SELECT), with the complexity of the data on top, I am finding this extremely confusing.</p>
  
  <p>Kind regards,</p>
  
  <p>James</p>