select d.orderId as id, a.firstname, a.secondname,
concat_ws(', ', b.street, b.city, b.postcode) as adress,
a.telephone, c.title as bookTitle,
concat_ws(' ', f.firstName, f.middleName, f.lastName) as bookAuthor1,
substring(group_concat(' ', concat_ws(' ', g.firstName, g.middleName, g.lastName)), length(concat_ws(' ', g.firstName, g.middleName, g.lastName))/2+3) as bookAuthor2,
c.pageCount
from books as c
left JOIN orders as d
on d.bookId = c.bookId
left join customers as a
on d.userId = a.id
left JOIN adresses as b
on a.id = b.userId
left join autorsbooks as e
on c.bookId = e.bookId
join autors as f
on e.authorId = f.id
join autors as g
on e.authorId = g.id
 GROUP BY d.orderId;


Тут запрос создаёт представления, но можно просто удалить все слова до select
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `lesson4_position3`
AS SELECT
   `orders`.`orderId` AS `id`,
   `customers`.`firstname` AS `firstname`,
   `customers`.`secondname` AS `secondname`,concat(`adresses`.`street`,', ',`adresses`.`city`,', ',`adresses`.`postcode`) AS `adress`,
   `customers`.`telephone` AS `telephone`,
   `books`.`title` AS `title`,concat(`autors`.`firstName`,' ',ifnull(`autors`.`middleName`,''),' ',`autors`.`lastName`) AS `bookAuthor1`,substr(substr(group_concat(`autors`.`firstName`,' ',ifnull(`autors`.`middleName`,''),' ',`autors`.`lastName` separator ','),locate(',',group_concat(`autors`.`firstName`,' ',ifnull(`autors`.`middleName`,''),' ',`autors`.`lastName` separator ','))),2) AS `bookAuthor2`,
   `books`.`pageCount` AS `pageCount`,
   `orders`.`dateOrder` AS `dateOrder`
FROM (((((`orders` join `customers` on((`orders`.`userId` = `customers`.`id`))) join `adresses` on((`customers`.`id` = `adresses`.`userId`))) join `books` on((`orders`.`bookId` = `books`.`bookId`))) join `autorsbooks` on((`books`.`bookId` = `autorsbooks`.`bookId`))) join `autors` on((`autorsbooks`.`authorId` = `autors`.`id`))) group by `orders`.`orderId`;


SELECT
Orders.orderId,
Customers.firstname,
Customers.secondname,
GROUP_CONCAT(Adresses.street, ', ', Adresses.city, ', ', Adresses.postcode) as 'adress',
Customers.telephone,
Books.title as 'bookTitle',
CONCAT(Autors.firstName,' ',COALESCE(Autors.middleName,''), ' ', Autors.lastName) as bookAuthor,
CASE
WHEN substring_index(GROUP_CONCAT(Autors.firstName,' ',COALESCE(Autors.middleName,''), ' ', Autors.lastName),',',-1) = CONCAT(Autors.firstName,' ',COALESCE(Autors.middleName,''), ' ', Autors.lastName)
THEN ''
ELSE substring_index(GROUP_CONCAT(Autors.firstName,' ',COALESCE(Autors.middleName,''), ' ', Autors.lastName),',',-1)
END as bookAuthor2,
Books.pageCount,
Orders.dateOrder
FROM Orders
join Customers
on Orders.userId = Customers.id
join Books
on Orders.bookId = Books.bookId
join AutorsBooks
on Books.bookId = AutorsBooks.bookId
join Autors
on Autors.id = AutorsBooks.id
join Adresses
on Customers.id = Adresses.userId
GROUP by Orders.orderId