title: sqlite3 JOIN, GROUP_CONCAT using distinct with custom separator
body: <p>Given a table of "events" where each event may be associated with zero or more "speakers" and zero or more "terms", those records associated with the events through join tables, I need to produce a table of all events with a column in each row which represents the list of "speaker_names" and "term_names" associated with each event.</p>
  
  <p>However, when I run my query, I have duplication in the speaker_names and term_names values, since the join tables produce a row per association for each of the speakers and terms of the events:</p>
  
  <pre><code>1|Soccer|Bobby|Ball
  2|Baseball|Bobby - Bobby - Bobby|Ball - Bat - Helmets
  3|Football|Bobby - Jane - Bobby - Jane|Ball - Ball - Helmets - Helmets
  </code></pre>
  
  <p>The group_concat aggregate function has the ability to use 'distinct', which removes the duplication, though sadly it does not support that alongside the custom separator, which I really need. I am left with these results:</p>
  
  <pre><code>1|Soccer|Bobby|Ball
  2|Baseball|Bobby|Ball,Bat,Helmets
  3|Football|Bobby,Jane|Ball,Helmets
  </code></pre>
  
  <p>My question is this: <strong>Is there a way I can form the query or change the data structures in order to get my desired results?</strong></p>
  
  <p>Keep in mind this is a sqlite3 query I need, and I cannot add custom C aggregate functions, as this is for an Android deployment.</p>
  
  <p>I have created a gist which makes it easy for you to test a possible solution: <a href="https://gist.github.com/4072840">https://gist.github.com/4072840</a></p>