you will find two tables in db

Book{
isbn integer, //primary key
title char(50),
publisher char(30),
year integer
}

Author{
isbn integer,  //foreign key 
name String,
rank integer,  // indicate whether it is author1, author2, author3,...,authorn
}

write a sql statement to output book in by doing this Title author1 author2 writer, year.

The logic is choosing two records from Author table based on isbn then mix towards the record in Book and output it. I believe we are able to get it done in MySQL?

THANKS.

Try GROUP_CONCAT

SELECT b.title, group_concat(a.name separator ',')
FROM book b join author a on b.isbn = a.isbn
group by b.title

You will find two sub query during my solution: Choose Book.title, (Choose Author.title FROM Author WHERE Author.isbn = Book.isbn AND Author.rank = '1') AS author1, (Choose Author.title FROM Author WHERE Author.isbn = Book.isbn AND Author.rank = '2') AS author2 FROM Book

can there be in whatever way to enhance performance? using, join, procedure, or any other way? thanks