SQL: 3 tables (book, author, book_author)

Question

I have 3 tables in my MySQL database which have the following structures:

Book table:

     BOOK
---------------
book_id | title
---------------
   1    |   A
   2    |   B

Author table:

     AUTHOR
----------------
author_id | name
----------------
     1    | John
     2    | Bush
     3    | Alex
     4    | Bob

And then I have a junction-table that establishes a many-to-many relationship between the tables book and author, which means that a book can be written by many authors (coauthored, that is) and an author can have many books he or she has penned.

    BOOK_AUTHOR
--------------------
book_id |  author_id
--------------------
   1    |     1
   1    |     2
   1    |     3
   1    |     4
   2    |     3
   2    |     4

Is it possible by means of SQL or MySQL to get the DBMS to output something like this:

 book_id |  title  |       authors
------------------------------------------
    1    |    A    | John, Bush, Alex, Bob
    2    |    B    | Alex, Bob

The authors row in the output is a concatenation of all the authors associated with a particular book.

Answer 1

Since you are using MySQL, use GROUP_CONCAT() to concatenate the rows for every group.

SELECT  a.Book_ID, 
        a.Title,
        GROUP_CONCAT(c.Name ORDER BY c.Name) Authors
FROM    Book a
        INNER JOIN book_author b
            ON a.Book_ID = b.Book_ID 
        INNER JOIn Author c
            ON b.Author_ID = c.Author_ID
GROUP   BY a.Book_ID, a.Title

• SQLFiddle Demo
• MySQL GROUP_CONCAT()

OUTPUT

╔═════════╦═══════╦════════════════════╗
║ BOOK_ID ║ TITLE ║      AUTHORS       ║
╠═════════╬═══════╬════════════════════╣
║       1 ║ A     ║ Alex,Bob,Bush,John ║
║       2 ║ B     ║ Alex,Bob           ║
╚═════════╩═══════╩════════════════════╝

Answer 2

That was very good explanation for many to many relation and 3 tables how to pull data from them.

Most importantly I want to give a customized solution for Oracle folks bcz Sql AND Oracle do not have same syntax and it will be hard to find on net... So Enjoy..

SELECT  book.BOOK_ID, book.BOOK_TITLE, 
listagg (CONCAT(CONCAT(author.AUTHOR_FIRSTNAME, ' '),author.AUTHOR_LASTNAME), ',') 
WITHIN GROUP 
(ORDER BY author.AUTHOR_FIRSTNAME) FirstName
FROM    Books book
        INNER JOIN authorbooks ab
            ON ab.BOOKS_ID = book.BOOK_ID
        INNER JOIN Authors author
            ON ab.Author_id = author.Author_ID
GROUP BY book.BOOK_ID, book.BOOK_TITLE;

This will print in a good format including first name and last name..