MySQL group_concat twice in SubQuery DISTINCT

Question

I have table like this:

title   name
1   ADAM SMITH
1   JACK SMITH
1   ROBERT ABADI
2   JACK SMITH
2   JAMES ANDERSON
3   JACK SMITH

When I call data with query looks like

SELECT title, name, group_concat(name) as gname
FROM sample
GROUP BY title

The result is:

1   ADAM SMITH  ADAM SMITH,JACK SMITH,ROBERT ABADI
2   JACK SMITH  JACK SMITH,JAMES ANDERSON
3   JACK SMITH  JACK SMITH

Now, I want to group by name again. so I call this query and results is following.

SELECT title, name, group_concat(distinct gname)
FROM
(
SELECT title, name, group_concat(name) as gname
FROM sample
GROUP BY title
) sub
GROUP BY sub.name


1   ADAM SMITH  ADAM SMITH,JACK SMITH,ROBERT ABADI
2   JACK SMITH  JACK SMITH,JAMES ANDERSON,JACK SMITH

But I don't want to have the 'JACK SMITH' twice on last column.

I know the reason because the first group_concat() results is string that can't not applied with 'DISTINCT' clause.

Any suggestions are appreciated.

Answer 1

Use a JOIN to get co-authors:

SELECT s1.name, GROUP_CONCAT(DISTINCT s2.name)
FROM sample AS s1
JOIN sample AS s2 ON s1.title = s2.title
GROUP BY s1.name

DEMO

If you don't want to show an author as a co-author of himself, add AND s1.name != s2.name to the ON condition.