CODEWITHSUNDEEP
mysqlconditional-statementssql-search
Dinesh
Dinesh

Reputation: 2204

MySQL conditional search query

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.

Upvotes: 0

Views: 166

Answers (2)

cyadvert
cyadvert

Reputation: 875

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()

Upvotes: 0

Rick James
Rick James

Reputation: 142518

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;

Upvotes: 0

Related Questions