title: Joining Two tables Based on Third Table (SQLite)
body: <p>I'm trying to use three different tables to get a list of all McDonald's in the US, sorted by number of Burger Kings within 100 miles. For some reason, I'm having a really hard time wrapping my head around this problem, and I'm wondering if anyone can offer any suggestions on how they might approach a problem like this?</p>
  <p>I have a table of all McDonald's with their zip codes, a table of all zip codes with number of burger kings, and a table of all zip codes within 100 miles of each other. For example:</p>
  <p><code>Mcdonalds</code> table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">NAME</th>
  <th style="text-align: center;">zip</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">McDonalds Park Terrace</td>
  <td style="text-align: center;">11121</td>
  </tr>
  <tr>
  <td style="text-align: center;">McDonalds HWY 103</td>
  <td style="text-align: center;">11412</td>
  </tr>
  <tr>
  <td style="text-align: center;">McDonalds Crestwood</td>
  <td style="text-align: center;">11121</td>
  </tr>
  <tr>
  <td style="text-align: center;">McDonalds Pendleton</td>
  <td style="text-align: center;">26566</td>
  </tr>
  <tr>
  <td style="text-align: center;">McDonalds Smithville</td>
  <td style="text-align: center;">67742</td>
  </tr>
  <tr>
  <td style="text-align: center;">McDonalds HWY 66</td>
  <td style="text-align: center;">43351</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><code>BKzips</code> table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">zip</th>
  <th style="text-align: center;">numberBKs</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">11121</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">11412</td>
  <td style="text-align: center;">0</td>
  </tr>
  <tr>
  <td style="text-align: center;">26566</td>
  <td style="text-align: center;">6</td>
  </tr>
  <tr>
  <td style="text-align: center;">43351</td>
  <td style="text-align: center;">0</td>
  </tr>
  <tr>
  <td style="text-align: center;">67742</td>
  <td style="text-align: center;">3</td>
  </tr>
  <tr>
  <td style="text-align: center;">...</td>
  <td style="text-align: center;">...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><code>zipproximity</code> table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">zip1</th>
  <th style="text-align: center;">zip2</th>
  <th style="text-align: center;">distancebetween</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">11121</td>
  <td style="text-align: center;">11412</td>
  <td style="text-align: center;">23.443</td>
  </tr>
  <tr>
  <td style="text-align: center;">11121</td>
  <td style="text-align: center;">26566</td>
  <td style="text-align: center;">48.211</td>
  </tr>
  <tr>
  <td style="text-align: center;">...</td>
  <td style="text-align: center;">...</td>
  <td style="text-align: center;">...</td>
  </tr>
  <tr>
  <td style="text-align: center;">11412</td>
  <td style="text-align: center;">11121</td>
  <td style="text-align: center;">23.443</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My desired end result is a table with a row for each mcdonalds, listing all zips within 100 mi of that mcdonalds and their total number of BKs. For example:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">McDonalds</th>
  <th style="text-align: center;">zip</th>
  <th style="text-align: center;">zipsWithBKs</th>
  <th style="text-align: center;">totalBKs</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">McDonalds Park Terrace</td>
  <td style="text-align: center;">11121</td>
  <td style="text-align: center;">26566, 11412</td>
  <td style="text-align: center;">6</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've been able to use inner joins to get a table with one row per zip with BKs:</p>
  <pre><code>SELECT McDonalds.NAME, BKzips.zip, BKzips.numberBKs
  FROM zipproximity
      INNER JOIN McDonalds
          ON zipproximity.zip1 = McDonalds.zip
      INNER JOIN BKzips 
          ON zipproximity.zip2 = BKzips.zip
  </code></pre>
  <p>But I can't figure out how to get my desired result. Between not being experienced with SQL and working this problem for hours I'm pretty stuck. I appreciate any help!</p>