title: Query to select subcategories of categories of destinations which are active?
body: <p>I have a table of destinations ( states ) which contain primary level categories and subcategories for activities. My ultimate goal is to output xml navigation links so I can link to:</p>
  
  <ol>
  <li>Each destination permalink ( ordered alphabetically by destination slug name )</li>
  <li>Each category underneath</li>
  <li>Each subcategories underneath the category</li>
  </ol>
  
  <p><strong>The below is what I'll generate with the sql query, incase it has any bearing on the query but I don't need xml help for this question</strong>:</p>
  
  <pre><code>&lt;item href="/destinations/alabama/"&gt;
      &lt;list&gt;
          &lt;item href="/destinations/alabama/category/"&gt;
              &lt;list&gt;
                 &lt;item href="/destinations/alabama/category/sub-category/"&gt;&lt;/item&gt;
              &lt;/list&gt;
          &lt;/item&gt;
      &lt;/list&gt;
  &lt;/item&gt;
  &lt;item href="/destinations/maryland/"&gt;
      &lt;list&gt;
          &lt;item href="/destinations/maryland/category/"&gt;
              &lt;list&gt;
                 &lt;item href="/destinations/maryland/category/sub-category/"&gt;&lt;/item&gt;
              &lt;/list&gt;
          &lt;/item&gt;
      &lt;/list&gt;
  &lt;/item&gt;
  </code></pre>
  
  <p>Currently I have a query that grabs subcategories but it doesn't account for active destinations. </p>
  
  <pre><code>SELECT
  
  subcategories.name AS subcategory_name,
  subcategories.slug AS subcategory_slug,
  categories.name AS category_name,
  categories.slug AS category_slug
  
  FROM
  
  subcategories
  
  LEFT JOIN destinations_subcategories ON
  destinations_subcategories.subcategory_id = subcategories.id
  
  LEFT JOIN categories ON
  destinations_subcategories.category_id = categories.id
  
  WHERE
  1=1 AND
  subcategories.is_active = 1 AND
  categories.is_active = 1
  </code></pre>
  
  <p>Which returns:</p>
  
  <pre><code>subcategory_name    subcategory_slug    category_name    category_slug
  Fly Fishing         fly-fishing         Fishing          fishing
  </code></pre>
  
  <p>But this query doesn't account for destinations which are active, since it's possible to map relationships between inactive destinations and active categories. I'm thinking I need to maybe have a subquery grabbing all active destinations, then do the joining? </p>
  
  <p><br><br><br><br>
  SCHEMA/row samples:</p>
  
  <pre><code>id    name       slug       active
  7     Maryland   maryland   1
  </code></pre>
  
  <p>My categories table, sample row:</p>
  
  <pre><code>id    name       slug       active
  1     Fishing    fishing    1
  </code></pre>
  
  <p>Subcategories table sample row:</p>
  
  <pre><code>id    name           slug           active
  3     Fly-Fishing    fly-fishing    1
  </code></pre>
  
  <p>And 2 tables to store relationships, the first maps subcategories to categories:</p>
  
  <pre><code>id    category_id    subcategory_id
  5     1              3
  </code></pre>
  
  <p>The second, <code>destinations_subcategories</code> maps destinations to subcategories:</p>
  
  <pre><code>id    destination_id   category_id    subcategory_id
  5     7                1              3
  </code></pre>