Two LEFT JOIN in select command

  
/*

mysql> SELECT ArticleTitle, Copyright, CONCAT_WS(' ', AuthorFirstName, AuthorMid
dleName, AuthorLastName) AS Author
    -> FROM Articles AS b LEFT JOIN AuthorArticle AS ab ON b.ArticleID=ab.Articl
eID
    ->    LEFT JOIN Authors AS a ON ab.AuthID=a.AuthID
    -> ORDER BY ArticleTitle;
+-------------------+-----------+-------------------+
| ArticleTitle      | Copyright | Author            |
+-------------------+-----------+-------------------+
| AI                |      1993 | Annie Peng        |
| Buy a paper       |      1932 | James Elk         |
| Buy a paper       |      1932 | Mary G. Lee       |
| Conferences       |      1996 | Nelson Yin        |
| How write a paper |      1934 | Tom M Ride        |
| Information       |      1992 | Annie Peng        |
| Journal           |      1980 | Jack K Ken        |
| Publish a paper   |      1919 |                   |
| Sell a paper      |      1966 | Henry S. Thompson |
+-------------------+-----------+-------------------+
9 rows in set (0.03 sec)


*/

Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;



CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;


INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
                            (13331, 'Publish a paper', 1919),
                            (14356, 'Sell a paper', 1966),
                            (15729, 'Buy a paper', 1932),
                            (16284, 'Conferences', 1996),
                            (17695, 'Journal', 1980),
                            (19264, 'Information', 1992),
                            (19354, 'AI', 1993);


CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthorFirstName VARCHAR(20),
   AuthorMiddleName VARCHAR(20),
   AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;


INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
                           (1007, 'Jason', 'Carol', 'Oak'),
                           (1008, 'James', NULL, 'Elk'),
                           (1009, 'Tom', 'M', 'Ride'),
                           (1010, 'Jack', 'K', 'Ken'),
                           (1011, 'Mary', 'G.', 'Lee'),
                           (1012, 'Annie', NULL, 'Peng'),
                           (1013, 'Alan', NULL, 'Wang'),
                           (1014, 'Nelson', NULL, 'Yin');


CREATE TABLE AuthorArticle (
   AuthID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, ArticleID),
   FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (1006, 14356), 
                              (1008, 15729), 
                              (1009, 12786), 
                              (1010, 17695),
                              (1011, 15729), 
                              (1012, 19264), 
                              (1012, 19354), 
                              (1014, 16284);
  
SELECT ArticleTitle, Copyright, CONCAT_WS(' ', AuthorFirstName, AuthorMiddleName, AuthorLastName) AS Author
FROM Articles AS b LEFT JOIN AuthorArticle AS ab ON b.ArticleID=ab.ArticleID
   LEFT JOIN Authors AS a ON ab.AuthID=a.AuthID
ORDER BY ArticleTitle;

Related examples in the same category

1.	LEFT JOIN tables
2.	'USING' command in LEFT JOIN
3.	Two LEFT JOIN
4.	Creating the table list with LEFT JOIN and then forming the linking connection with ON
5.	A LEFT JOIN and a regular join
6.	To list each author record, whether or not there are any book records for it, use a LEFT JOIN
7.	To force each category to be displayed, use a reference table and a LEFT JOIN.
8.	Creating Left Joins
9.	Replacing the ON clause with the USING clause for Left Join
10.	Use a left join to link more than two tables.
11.	Addition of the LEFT keyword to each join definition
12.	Left Joins (Left Outer Joins)
13.	Performing a LEFT JOIN on just the customer and sales tables.
14.	Table order in a LEFT JOIN is important.
15.	Left outer join syntax
16.	Left outer join then order
17.	Left outer join
18.	Left outer join with using clause
19.	Left outer join with subquery
20.	Query from left outer join
21.	To return all the sales reps who have not yet made a sale

Two LEFT JOIN in select command : Left Join « Join « SQL / MySQL

Related examples in the same category