How to find all those authors wrote a paper together?

Question

How can I write an SQL query to find those authors who has co-authored some paper together.

schema :

Authors (
    authorID,
    firstName,
);
Papers (
    paperID,
    title,
);
PaperbyAuthor (
    authorID FOREIGN KEY REFERENCES Authors,
    paperID FOREIGN KEY  REFERENCES Papers
);

clinomaniac · Accepted Answer · 2018-03-15T00:27:08.830

2

This will list all the Authors with their PaperID for Authors that have worked on a paper together.

SELECT PA.paperID, GROUP_CONCAT(A.firstName separator ',') FROM PaperbyAuthor PA
INNER JOIN Authors A ON PA.authorID = A.authorID
WHERE paperID IN (SELECT paperID FROM PaperbyAuthor PA
GROUP BY paperID
HAVING COUNT(*) > 1)

You can also join the table with Papers if you need to see the title of the paper.

edited Mar 15 '18 at 00:27

answered Mar 14 '18 at 23:50

clinomaniac

2,200
2
17
22

1

Yes. You can use group concat. If you want it in separate columns then you need to consider a scenario where more than 2 authors have worked on a paper. – clinomaniac Mar 15 '18 at 00:00
@clinomanic query returned as `GROUP_CONCAT is not a recognized built-in function name.` – USERSFU Mar 15 '18 at 00:34
1

Are you using mysql or sqlserver? The question is tagged with both and I didn't realize that earlier. GROUP_CONCAT is only for mysql – clinomaniac Mar 15 '18 at 00:35
i am using sqlserver – USERSFU Mar 15 '18 at 00:36
Take a look at this post to see how to do this in sqlserver. https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server – clinomaniac Mar 15 '18 at 00:39

score 1 · Answer 2 · answered Mar 15 '18 at 00:04

This will give you every pair of authors twice: if John and Joe co-write a paper, there will be entries for

CoAuthor1   CoAuthor2   JointPapers 
JOHN        JOE         1
JOE         JOHN        1

The code is:

SELECT
    a1.firstName AS CoAuthor1,
    a2.firstName AS CoAuthor2,
    l.JointPapers
FROM
    (
    SELECT
        pba1.authorID AS CoAuthorID1,
        pba2.authorID AS CoAuthorID2,
        COUNT(*) AS JointPapers
    FROM
        PaperByAuthor AS pba1
    LEFT JOIN
        PaperByAuthor AS pba2
    ON
        pba1.paperID = pba2.paperID
    GROUP BY
        pba1.authorID,
        pba2.authorID
    ) AS l  
LEFT JOIN
    Authors AS a1
ON
    l.CoAuthorID1 = a1.authorID
LEFT JOIN
    Authors AS a2
ON
    l.CoAuthorID2 = a2.authorID

Racil Hilan · Answer 3 · 2018-03-14T23:47:35.370

0

If you want the authors who co-authored a specific paper, try:

SELECT a.authorID, a.firstName
FROM Authors a
INNER JOIN PaperbyAuthor pa ON pa.authorID = a.authorID
INNER JOIN Papers p ON p.paperID = pa.paperID
WHERE p.paperID = 1000;

Change the value 1000 to ID of the paper that you want. If you want to find the paper by title, change the last line to:

WHERE p.title = 'The Best Paper';

edited Mar 14 '18 at 23:47

answered Mar 14 '18 at 23:43

Racil Hilan

24,690
13
50
55

2

I don't think this is what OP wanted. I believe OP was asking for a list of authors that have worked on the same paper together. This returns the authors that have worked on a specific paper. – clinomaniac Mar 14 '18 at 23:47

score 0 · Answer 4 · answered Mar 14 '18 at 23:59

0

Are you just looking for pairs of authors?

select distinct min(authorID), max(authorID)
from PapersByAuthor pa
group by paperID
having count(authorID) = 2

answered Mar 14 '18 at 23:59

shawnt00

16,443
3
17
22

How to find all those authors wrote a paper together?

4 Answers4