Q:

How to display several values filtered from two columns

I have homework where I have to select data from two tables (book and author). Books has two authors and i need to display result with book title and authors separated by comma.
I tried by myself but I got errors.
Schema SQL:
CREATE TABLE IF NOT EXISTS `author` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128),
  `surname` VARCHAR(128),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `book` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(128),
  `author_id` INT NOT NULL,
  `author_id_2` INT NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;

Query SQL:
INSERT INTO `author` (`name`, `surname`) VALUES ("William", "Shakespeare");
INSERT INTO `author` (`name`, `surname`) VALUES ("Agatha", "Christie");
INSERT INTO `author` (`name`, `surname`) VALUES ("J. K", "Rowling");
INSERT INTO `author` (`name`, `surname`) VALUES ("George", "Orwell");
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Hamlet", 1, 2);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Macbeth", 1, 3);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Murder on the Orient Express", 2, 3);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("The Secret of Chimneys", 2, 1);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("1984", 3, 1);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Animal Farm", 3, 2);

Query:
SELECT 
    b.title Pavadinimas, 
    concat(concat(a1.name, ' ', a1.surname), ', ', concat(a2.name, ' ', a2.surname) Autoriai 
FROM book b 
INNER JOIN author a1 WHERE book.author_id=a1.id 
INNER JOIN author a2 WHERE book.author_id_2=a2.id;

The result should be:
Hamlet | William Shakespeare, Agatha Christie

A:

You would need to join twice the book table with the author table, like:
SELECT 
    b.title, 
    CONCAT(a1.name, ' ', a1.surname, ', ', a2.name, ' ', a2.surname)  authors
FROM book b 
INNER JOIN author a1 ON b.author_id = a1.id
INNER JOIN author a2 ON b.author_id_2 = a2.id

This demo on DB Fiddle with your sample data returns:
| title                        | authors                              |
| ---------------------------- | ------------------------------------ |
| Hamlet                       | William Shakespeare, Agatha Christie |
| Macbeth                      | William Shakespeare, J. K Rowling    |
| Murder on the Orient Express | Agatha Christie, J. K Rowling        |
| The Secret of Chimneys       | Agatha Christie, William Shakespeare |
| 1984                         | J. K Rowling, William Shakespeare    |
| Animal Farm                  | J. K Rowling, Agatha Christie        |

NB: if you have books with just one author, you can use LEFT JOIN instead of INNER JOIN.

