By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Authors (
`Author_ID` INTEGER,
`Author_name` VARCHAR(4),
`Paper_ID` INTEGER
);
INSERT INTO Authors
(`Author_ID`, `Author_name`, `Paper_ID`)
VALUES
('1', 'Jack', '313'),
('2', 'Ray', '313'),
('2', 'Ray', '458'),
('3', 'Amy', '458'),
('1', 'Jack', '458'),
('2', 'Ray', '419'),
('3', 'Amy', '419'),
('2', 'Ray', '619'),
('3', 'Amy', '619');
Records: 9 Duplicates: 0 Warnings: 0
SELECT a1.Author_name AuthorA,
a2.Author_name AuthorB,
COUNT(*) JointPapers
FROM Authors a1 INNER JOIN Authors a2
ON a1.Paper_ID = a2.Paper_ID AND a1.Author_ID < a2.Author_ID
GROUP BY a1.Author_name, a2.Author_name;
| AuthorA | AuthorB | JointPapers |
|---|---|---|
| Jack | Ray | 2 |
| Jack | Amy | 1 |
| Ray | Amy | 3 |
SELECT a1.Author_ID IDA, a1.Author_name AuthorA,
a2.Author_ID IDB, a2.Author_name AuthorB,
COUNT(*) JointPapers
FROM Authors a1 INNER JOIN Authors a2
ON a1.Paper_ID = a2.Paper_ID AND a1.Author_ID < a2.Author_ID
GROUP BY a1.Author_ID, a1.Author_name, a2.Author_ID, a2.Author_name;
| IDA | AuthorA | IDB | AuthorB | JointPapers |
|---|---|---|---|---|
| 1 | Jack | 2 | Ray | 2 |
| 1 | Jack | 3 | Amy | 1 |
| 2 | Ray | 3 | Amy | 3 |