title: Hierarchical Data in MySQL
body: <p>I've got a sort of tree like thing going on in my MySQL database.</p>
  
  <p>I have a database that has categories, and each category has a subcat. I'm keeping all the categories in one table, so the columns are like this:</p>
  
  <pre><code>*categories table*
  id | name  | parent_id
  1  | Toys  | 0
  2  | Dolls | 1
  3  | Bikes | 1
  </code></pre>
  
  <p>Each item in my database is assigned to one of those categories:</p>
  
  <pre><code>*items table*
  item   | category_id
  barbie | 2
  schwinn| 3
  </code></pre>
  
  <p>The problem is if someone wants to see all TOYS (the parent category) what is the best way to fetch the info from the items database? The only way I know how is to do something like</p>
  
  <pre><code>SELECT * 
  FROM items 
  WHERE category_id = 2 
  JOIN SELECT * 
       FROM items 
       WHERE category_id = 3
       etc... 
  </code></pre>
  
  <p>But if I had like 10 categories under Toys, then I'd have to do this join and query 10 times.</p>
  
  <p>Is there a better way to handle this?</p>