title: MySQL: Select Category and (optional) Sub-Category for Projects in One Statement
body: <p>In my MySQL-Database, I have two tables, one for the projects and one for the categories:</p>
  
  <p>The projects-table looks something like this:</p>
  
  <pre><code> id | project_title        | category_id
   ---|----------------------|------------
    1 | My Book Project      | 101
    2 | My Comic Project     | 102
    3 | My Magazine Project  | 104
  </code></pre>
  
  <p>Then I have the categories. These can have a parent category which is stored in the same table:</p>
  
  <pre><code> id | category_title     | parent_id  
   ---|--------------------|----------
  101 | Books              | 0
  102 | Comics             | 101
  103 | DVDs               | 0
  104 | Magazines          | 101
  </code></pre>
  
  <p>I like to fetch all the projects with the according category and (optional) sub-category (if <code>parent_id</code> is provided). If the category has no sub-category, the sub-category should be something like "-" or "none".</p>
  
  <p>Now I know how I can get all these values with several statements:</p>
  
  <p><strong>First Statement:</strong> Fetch the projects with the indicated category (which can be a main-category or a sub category, therefor I fetch also the category's <code>parent_id</code>):</p>
  
  <pre><code>SELECT
      p.project_title, 
      c.category_title,
      c.parent_id as cat_parent_id
  FROM
      projects p, 
      categories c
  WHERE
      p.category_id = c.id
  </code></pre>
  
  <p><strong>Second Statement(s):</strong> After that, I could fetch the possible sub-category of a project within a loop, or assign the found category as main-category, e.g. with php:</p>
  
  <pre><code>&lt;?php
  foreach( $rows as $project ) {
      if ( $project['cat_parent_id'] &gt; 0 ) {
          $project['sub_category'] = $project['category_title'];
          // query main-category here
      }
      else {
          $project['main_category'] = $project['category_title'];
          $project['sub_category'] = 'none';
      }
  
      // do things with this project ...
  }
  ?&gt;
  </code></pre>
  
  <p><strong>The problem</strong> is that I will have another query for each project found, and this is not nice. I think there should be a way to fetch all the required values in one statement. I found <a href="https://stackoverflow.com/questions/3668363/select-categories-with-parent-name-from-one-table">this question</a> on SO which is nearly the same, but in my case, the sub-category is obtional.</p>
  
  <p>As John Cleese would say: Could someone please give me a push?</p>