title: Only join tables if a certain condition is met
body: <p>I'm trying to write a SQLite query that only performs a JOIN if a certain condition is met (items.super = 1). I know I could write some code in my application to figure out if super == 1 and then execute the correct query, but I'd much rather just have one query that works in both cases--whether super is 0 or 1.</p>
  
  <pre><code>SELECT i2.id, i2.name
  FROM items i
  JOIN items i2 ON i.subcategory_id = i2.subcategory_id AND i.super = 1
  WHERE i.id = ?
  </code></pre>
  
  <p>Above, I tried to add a second condition to my JOIN clause, but that doesn't work when i.super == 0. Should I solve this with something wacky like two left joins and the coalesce() function?</p>
  
  <p>Thanks.</p>