title: Combine data from two different junction tables
body: <p>Part of my schema is as pictured<img src="https://i.stack.imgur.com/CV7OV.png" alt="Category, Items"></p>
  
  <p>I have two junction tables, one of which maps Items to Categories and the second which maps what items are "featured" for a particular category.</p>
  
  <p>What I need to do now is get <em>all</em> items that are in a particular category (not difficult), but I also need to have a column "featured" that would display if the item is featured for that category (NULL if not featured).</p>
  
  <p>I've tried various combinations of LEFT JOIN for example</p>
  
  <pre><code>SELECT i.*, category.name, category_feat_item.item_id AS featured
  FROM item AS i
  INNER JOIN item_category ON i.id = item_category.item_id
  INNER JOIN category ON category.id = item_category.category_id AND category.id =1
  LEFT OUTER JOIN category_feat_item ON i.id = category_feat_item.item_id
  ORDER BY featured DESC
  </code></pre>
  
  <p>but I'm stumped.</p>