title: How to join through an intermediary table transparently
body: <h2>Simple version</h2>
  <p>Let's say I have two tables:</p>
  <p><strong>Contacts</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Bob</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Sally</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Jane</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Activities</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>contact_id</th>
  <th>type</th>
  <th>subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>3</td>
  <td>Meeting</td>
  <td>Strategy planning</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>Phone Call</td>
  <td>Follow-up on lead</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>Phone Call</td>
  <td>Second follow-up</td>
  </tr>
  <tr>
  <td>4</td>
  <td>3</td>
  <td>Meeting</td>
  <td>Strategy planning 2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I can do a simple <code>LEFT JOIN</code> to retrieve contacts who may have an activity of a certain type:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT name, subject
  FROM Contacts c
  LEFT JOIN Activities a ON a.contact_id = c.id AND a.type = 'Meeting'
  </code></pre>
  <p>This will give me all my contacts, with their meetings if they have any:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>name</th>
  <th>subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Bob</td>
  <td><em>null</em></td>
  </tr>
  <tr>
  <td>Sally</td>
  <td><em>null</em></td>
  </tr>
  <tr>
  <td>Jane</td>
  <td>Strategy planning</td>
  </tr>
  <tr>
  <td>Jane</td>
  <td>Strategy planning 2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Notice that I get 2 rows for Jane because she has 2 meetings. I <em>do not</em> get 2 rows for Bob, even though he has 2 activities, because they are excluded by the ON clause. I also get a row for Sally, even though she doesn't have any activities of any kind.</p>
  <h2>With extra join table</h2>
  <p>Ok, that's what I want to be able to do, but the monkey-wrench is that my schema doesn't look like that. It has an intermediary table (because more than one contact could be part of an activity). The real schema looks like the above but with no <code>contact_id</code> column for Activities:</p>
  <p><strong>Contacts</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Bob</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Sally</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Jane</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Activities</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>type</th>
  <th>subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Meeting</td>
  <td>Strategy planning</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Phone Call</td>
  <td>Follow-up on lead</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Phone Call</td>
  <td>Second follow-up</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Meeting</td>
  <td>Strategy planning 2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Activity_Contact</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>activity_id</th>
  <th>contact_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>4</td>
  <td>3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And this is where I'm stuck. How do I perform the same type of <code>LEFT JOIN</code> from Contacts to Activities? If I add two joins to the query, then I end up with duplicate rows for Bob (because he has 2 entries in the Activity_Contact table, even though the 2nd join excludes his activities):</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT name, subject
  FROM Contacts c
  LEFT JOIN Activity_Contact ac ON ac.contact_id = c.id
  LEFT JOIN Activities a ON a.id = ac.activity_id AND a.type = 'Meeting'
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>name</th>
  <th>subject</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Bob</td>
  <td><em>null</em></td>
  </tr>
  <tr>
  <td>Bob</td>
  <td><em>null</em></td>
  </tr>
  <tr>
  <td>Sally</td>
  <td><em>null</em></td>
  </tr>
  <tr>
  <td>Jane</td>
  <td>Strategy planning</td>
  </tr>
  <tr>
  <td>Jane</td>
  <td>Strategy planning 2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I could use <code>GROUP BY</code> to get only one Bob, but that would squash the rows for Jane as well.</p>
  <p>What I really want is to transparently join through the intermediary table as if it wasn't even there. Is there a way to do that in MySql?</p>