CODEWITHSUNDEEP
sqlsql-server
USERSFU
USERSFU

Reputation: 69

How to find all those authors wrote a paper together?

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

Upvotes: 0

Views: 797

Answers (4)

clinomaniac
clinomaniac

Reputation: 2218

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.

Upvotes: 2

tysonwright
tysonwright

Reputation: 1525

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

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

Are you just looking for pairs of authors?

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

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25351

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';

Upvotes: 0

Related Questions