CODEWITHSUNDEEP
mysqlsql
Mona
Mona

Reputation: 1495

SQL: how to join field records within one table to create a new table?

I am currently learning SQL queries and have a question as follows

There is a table Authored for this with three attributes,

id author_id publication_id

Find the top 20 most collaborative authors. That is, for each author determine its number of collaborators, and then find the top 20.

I am thinking of using this table to generate a temporary table of co-authors, i.e. 

author_id coauthor_id
1 2
1 3
1 7
2 1
2 8
2 10

and then count the number of coauthors for each author and then find the max.

As there is only one table, operations like JOIN cannot be used. Anyone can help to give some suggestions?

Upvotes: 1

Views: 147

Answers (2)

andrewdotn
andrewdotn

Reputation: 34863

You can absolutely use JOIN when there’s only one table. This is a very common way of getting data out of databases.

Example:

mysql> CREATE TABLE Authored (id INT, author_id INT, publication_id INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Authored
    -> SELECT 1, 1, 1
    -> UNION ALL
    -> SELECT 2, 2, 1
    -> UNION ALL
    -> SELECT 3, 1, 2;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Now we have an authored table, and we want to join it to itself in order to see all the instances where multiple authors contributed to the same publication:

mysql> SELECT a1.publication_id, a1.author_id AS author1, a2.author_id AS author2
    -> FROM Authored AS a1, Authored AS a2
    -> WHERE a1.publication_id = a2.publication_id
    -> AND a1.author_id != a2.author_id;
+----------------+---------+---------+
| publication_id | author1 | author2 |
+----------------+---------+---------+
|              1 |       2 |       1 |
|              1 |       1 |       2 |
+----------------+---------+---------+
2 rows in set (0.00 sec)

From here, you will be able to group by author, count the results, and sort them.

Upvotes: 2

Amazigh.Ca
Amazigh.Ca

Reputation: 3543

select count(coauthor_id) as nbr, author_id from co-authors
group by author_id 
order by nbr
limit 20;

Upvotes: 0

Related Questions