Two USING clauses, rather than ON clauses
mysql> mysql> CREATE TABLE Books -> ( -> BookID SMALLINT NOT NULL PRIMARY KEY, -> BookTitle VARCHAR(60) NOT NULL, -> Copyright YEAR NOT NULL -> ) -> ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO Books VALUES -> (12786, 'Notebook', 1934), -> (13331, 'C++', 1919), -> (14356, 'Opera', 1966), -> (15729, 'Sql Server', 1932), -> (16284, 'C', 1996), -> (17695, 'Pascal', 1980), -> (19264, 'Postcards', 1992), -> (19354, 'Oracle', 1993); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> CREATE TABLE Authors -> ( -> AuthID SMALLINT NOT NULL PRIMARY KEY, -> AuthFN VARCHAR(20), -> AuthMN VARCHAR(20), -> AuthLN VARCHAR(20) -> ) -> ENGINE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO Authors VALUES -> (1006, 'Hunter', 'S.', 'Thompson'), -> (1007, 'Joyce', 'Carol', 'Oates'), -> (1008, 'Black', NULL, 'Elk'), -> (1009, 'Rainer', 'Maria', 'Rilke'), -> (1010, 'John', 'Kennedy', 'Toole'), -> (1011, 'John', 'G.', 'Neihardt'), -> (1012, 'Annie', NULL, 'Proulx'), -> (1013, 'Alan', NULL, 'Watts'), -> (1014, 'Nelson', NULL, 'Algren'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> CREATE TABLE AuthorBook -> ( -> BookID SMALLINT NOT NULL, -> AuthID SMALLINT NOT NULL, -> PRIMARY KEY (AuthID, BookID) -> ) -> ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO AuthorBook VALUES -> (1006, 14356), -> (1008, 15729), -> (1009, 12786), -> (1010, 17695), -> (1011, 15729), -> (1012, 19264), -> (1012, 19354), -> (1014, 16284); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> mysql> SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author -> FROM Books JOIN AuthorBook USING (BookID) -> JOIN Authors USING (AuthID) -> WHERE Copyright<1980 -> ORDER BY BookTitle; Empty set (0.00 sec) mysql> mysql> mysql> drop table AuthorBook; Query OK, 0 rows affected (0.00 sec) mysql> drop table Authors; Query OK, 0 rows affected (0.00 sec) mysql> drop table Books; Query OK, 0 rows affected (0.00 sec) mysql> mysql>