MySQL GROUP_CONCAT on 3 way join
<p>I have 3 tables:</p>

<p><strong>authors</strong></p>

<pre><code>author_id    last_name    first_name    organization
1            Smith        John          Org A
2            Smith        Mary          Org A 
3            Doe          John          Org B   
4            Doe          Jane          Org C
5            Jones        John          Org C
6            Sarduchi     Guido         Org A
</code></pre>

<p><strong>author__papers</strong></p>

<pre><code>author_id    paper_id    is_conctact_author
1            1001        0
2            1001        0
3            1001        0 
4            1002        0
5            1002        0
6            1001        1 
</code></pre>

<p><strong>org_info (this is already filtered on is_contact_author &lt; 1)</strong></p>

<pre><code>paper_id    organization   idx    org_count 
1001        Org A          1      2
1001        Org B          2      2    
1002        Org C          1      1
</code></pre>

<p>What I need is a result set that looks like this based on the sample data above:</p>

<pre><code>paper_id    organization    last_names      idx    org_count
1001        org A           Smith, Smith    1      2
1001        org B           Doe             2      2
1002        Org C           Doe, Jones      1      1 
</code></pre>

<p>I get close with:</p>

<pre><code>select AP.paper_id as paper_id, organization
  GROUP_CONCAT(last_name SEPARATOR ', '), idx, org_count
from authors A
inner join authors__papers AP ON A.author_id = AP.author_id
inner join org_info O ON O.paper_id = AP.paper_id AND O.organization = A.organization
where AP.is_contact_author &lt; 1
group by O.organization, AP.paper_id
order by paper_id;
</code></pre>

<p>...but the last names are duplicated. If I add a DISTINCT to the GROUP_CONCAT, I loose the values where the last name is the same (e.g. Smith) even though it's a different person.</p>

<p>Any thought on how best to take this sample data and get the desired output without the issues?</p>

<p>TIA!</p>