Getting many to many queries to wsork

[Cecil Carpenter]

I cannot seem to get a many to many query to work. Here are my table definitions.

CREATE TABLE `book` (
`book_id` int NOT NULL AUTO_INCREMENT,
`bk_title` varchar(100) DEFAULT NULL,
`bk_edition` varchar(45) DEFAULT NULL,
`bk_year` year DEFAULT NULL,
`bk_isbn` varchar(45) DEFAULT NULL,
`bk_type` enum('H','T','F','P','') DEFAULT NULL,
`author_id` int NOT NULL,
`publisher_id` int NOT NULL,
PRIMARY KEY (`book_id`),
KEY `author_id-indx` (`author_id`),
CONSTRAINT `fk_books_1` FOREIGN KEY (`author_id`) REFERENCES `author` (`author_id`)
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `author` (
`author_id` int NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) DEFAULT NULL,
`middle_name` varchar(45) DEFAULT NULL,
`last_name` varchar(45) DEFAULT NULL,
`suffix` varchar(45) DEFAULT NULL,
`preferred_name` varchar(45) DEFAULT NULL,
`birth` date DEFAULT NULL,
`death` date DEFAULT NULL,
`book_id` varchar(45) DEFAULT NULL,
PRIMARY KEY (`author_id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `publisher` (
`publisher_id` int NOT NULL AUTO_INCREMENT,
`pub_name` varchar(45) DEFAULT NULL,
`pub_address` varchar(45) DEFAULT NULL,
`pub_city` varchar(45) DEFAULT NULL,
`pub_state` char(2) DEFAULT NULL,
`pub_zip` char(10) DEFAULT NULL,
`imprint_id` int DEFAULT NULL,
PRIMARY KEY (`publisher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `bookauthor` (
`book_id` int NOT NULL,
`author_id` int NOT NULL,
PRIMARY KEY (`book_id`,`author_id`),
KEY `author_id` (`author_id`),
KEY `Book_id` (`book_id`),
CONSTRAINT `bookauthor_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`book_id`),
CONSTRAINT `bookauthor_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `author` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The many to many relationshipsn are between the book table and the author table.

The bookauthor table is my junction table. The query I am trying to get to work is

SELECT b.bk_title as 'Title', a.preferred_name as 'Author', a.last_name as '', a.suffix as '', b.bk_year as 'Publish Date',
b.bk_edition as 'Edition', b.bk_type as 'Type', b.bk_isbn as 'ISBN', p.pub_name as 'Publisher'
from author a, book b, publisher p
join book on bookauthor.book_id = b.book_id
join author on bookauthor.author_id = a.author_id

I get the following rersponse:

Error Code: 1054. Unknown column 'bookauthor.book_id' in 'on clause'

Where am I going wrong? BTW, this is my first effort dealing with a many to many relationship query.