MySQL conditional search query

Question

I am trying to get all the co-authors of an author. The first column is the id of a publication. The next three columns are the first, middle and last names of an author.

4 KARL K KWON
4 JACK A SMITH  
4 DINESH  SMITH  
5 KARL K KWON
5 JACK B SMITH
6 AMEY SCHENCK BAILEY
6 KARL K KWON
6 DINESH  SMITH
6 JACK  SMITH
13 JACK  SMITH
13 RONALD  VALE

I would like to get the following output

JACK A SMITH {DINESH SMITH, KARL K KWON}
JACK B SMITH {KARL K KWON}
JACK  SMITH {AMEY SCHENCK, KARL K KWON, DINESH SMITH, RONALD  VALE}

I am unable to understand which structure to use to store this new data and how to obtain it. Any suggestions/ideas...

Edit

This is not a simple group by publication id. For example, JACK SMITH has 2 publications here with ids 6 and 13. So the coauthors of both these publications need to be found and added to our set of co-authors for JACK SMITH.

It seems like, you can get the result by grouping publication id — Shafeeque, Mar 05 '15 at 04:29
Is there a particular reason why Jack comes first in your list? — AdamMc331, Mar 05 '15 at 05:59
@McAdam331 This is sample mock data - not exact data from the database — Dinesh, Mar 05 '15 at 14:43
Still not clear. Karl Kwon has two publications, why is he not separated? — AdamMc331, Mar 05 '15 at 16:54
@McAdam331 I do not understand what you mean by separated? The output which is displayed is sample output which needs to be computed for all authors. — Dinesh, Mar 05 '15 at 20:24

Rick James · Answer 1 · 2015-03-10T06:59:49.743

0

This is not exactly the same format, but will it do?

Edit Try #2:

Add these indexes:

INDEX(pub_id, name)
INDEX(name, pub_id)

This is the query, I think this self-join should do the trick:

SELECT  a.name AS Author,
        GROUP_CONCAT(DISTINCT c.name SEPARATOR ', ') AS Co_authors
    FROM  ( SELECT  DISTINCT name FROM  tbl ) AS a  -- Get the authors
    JOIN  tbl b ON (b.name = a.name)                -- Get what they published
    JOIN  tbl c ON (c.pub_id = b.pub_id)            -- Get the co-authors
    GROUP BY  a.name;

edited Mar 10 '15 at 06:59

answered Mar 05 '15 at 05:19

Rick James

135,179
13
127
222

Please see my edit above. Hope that makes the question more clear. – Dinesh Mar 05 '15 at 14:57
Where did ADAM SMITH come from? – Rick James Mar 10 '15 at 06:46

score 0 · Answer 2 · answered Mar 10 '15 at 07:07

This will return ids and comma separated list of authors

SELECT id, GROUP_CONCAT(CONCAT_WS(' ', firstName, middleName, lastName)) FROM table GROUP BY id

Join the table with the one above

SELECT t.*, tGrouped.authors  FROM table t
JOIN (SELECT id, GROUP_CONCAT(CONCAT_WS(' ', firstName, middleName, lastName)) as authors FROM table GROUP BY id) tGrouped ON t.id=tGrouped.id

Finally, if you do not want a name to appear within authors list - use simple REPLACE()

MySQL conditional search query

2 Answers2