Example of intermediate table in many to many relationship in MySQL

For example, in pubmed database, we have article database. An article can have one or more authors and an author can be listed for one or more papers (many-to-many).  The intermediate  table "author_article" table is used to establish this relationship. The diagram is shown as follows:

Command for Creating `author` Table

CREATE TABLE `author` (
`author_id` int NOT NULL AUTO_INCREMENT,
`author_name` varchar(100) NOT NULL,
PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Command for Creating `article` Table

CREATE TABLE `article` (

 `pubmed_id` int NOT NULL,

 `article_title` varchar(100) NOT NULL,
`journal_title` varchar(100) NOT NULL,
`publication_year` date,
`article_abstract` text,
PRIMARY KEY (`pubmed_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Command for Creating intermediate table `author_article` 

CREATE TABLE `author_article` ( 

 `author_id` int NOT NULL, 

 `pubmed_id` tinyint NOT NULL,
`id` int NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Once relationships are stored, you can fetch data like below.

SELECT a.author_name, c.article_title FROM `author`
AS a LEFT JOIN `author_article`
AS b ON a.author_id = b.author_id LEFT JOIN `article`
AS c ON b.pubmed_id = c.pubmed_id;

Video: Example of intermediate table in many to many relationship in MySQL