title: MySQL: Category Tree and Products in n levels
body: <p>So, let's assume I've a MySQL DB with the following tables:</p>
  
  <p><strong>Product</strong></p>
  
  <ul>
  <li>id</li>
  <li>name</li>
  <li>category_id</li>
  </ul>
  
  <p><strong>Category</strong></p>
  
  <ul>
  <li>id</li>
  <li>name</li>
  <li>parent_id</li>
  </ul>
  
  <p>What's the best way to query the DB in order to get all products going down from a certain category id. For instance, if I've a tree of sub-categories where the base category id = <code>1</code> how can I get all the products under the subcategories of id =<code>1</code> for an undetermined number of sub-categories. </p>
  
  <p>I could do this:</p>
  
  <pre><code>SELECT * FROM `Product` WHERE category_id IN (
     SELECT `id` FROM `Category` WHERE parent_id = 1
  )
  </code></pre>
  
  <p>However it only works for the direct children of category id = <code>1</code> and not for the 2nd to n level children.</p>
  
  <p>Thank you.</p>
  
  <hr>
  
  <p><strong>Edit</strong></p>
  
  <p>Some people suggested to read <a href="http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/" rel="nofollow">a blog article</a> about this, I had a look at that article on the past also, and I made this sqlfiddle:</p>
  
  <p><a href="http://sqlfiddle.com/#!2/be72ec/1" rel="nofollow">http://sqlfiddle.com/#!2/be72ec/1</a></p>
  
  <p>As you can see on the query, even the simplest method they teach, to get a tree of categories doesn't output anything. What am I missing? The other methods have the same issue.</p>
  
  <p>Thank you.</p>