title: How to Query Nested Set model with multiple roots plus filtering
body: <p>How do you query a nested set model with multiple roots, such trees in the same table? Currently, I added an extra column called the "Root" indicating the ID of the root node for all sub-tree nodes, however, I can't figure out the sql to retrieve them in the proper order</p>
  
  <p>I'm referring to the article <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow noreferrer">Managing Hierarchical Data in MySQL</a>.</p>
  
  <p>Normally, the query to retrieve the items out in order is by order the left leaf value, but with multiple roots, you can end up with multiple "left:1" one after another, such break the tree. </p>
  
  <p>I'm currently using a solution completely not related to SQL. I re-arranged them in my C# code, but I'm just wondering if there is a way to do it with SQL and such save the time I spent on the web server</p>
  
  <p>One last question. If I have a filter and it filters out some data out of the tree, how do you deal with it? </p>
  
  <p>Say </p>
  
  <ul>
  <li>Task 1 (In progress)
  
  <ul>
  <li>Task 2 (Active)
  
  <ul>
  <li>Task 3 (Active)</li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>If the filter is to show all tree with status "Active", what do you do?</p>