Join with multiple tables and GROUP_CONCAT

Question

I have three main tables: books, authors, publishers and two connecting tables: authors_on_books & books_on_publishers.

I'm trying to get all the data using the following query:

select books.*, 
        GROUP_CONCAT(authors.id) as author_id, GROUP_CONCAT(authors.name) as author_name,
        GROUP_CONCAT(publishers.id) as publisher_id, GROUP_CONCAT(publishers.name) as publisher_name

        from books
        join authors_on_books on authors_on_books.book_id = books.id
        join authors on authors_on_books.author_id = authors.id
        join books_on_publishers on books_on_publishers.book_id = books.id
        join publishers on books_on_publishers.publisher_id = publishers.id
        group by books.id

The result I'm getting is:

[id] => 1
[title] => Title 1
[description] => This is a book
[year_published] => 1999
[isbn] => 11111111
[author_id] => 1,1,2,2,3,3
[author_name] => author 1,author 1,author 2,author 2,author 3,author 3
[publisher_id] => 1,1,1,1,1,1
[publisher_name] => publisher 1,publisher 1,publisher 1,publisher 1,publisher 1,publisher 1

But the desired outcome is:

[id] => 1
[title] => Title 1
[description] => This is a book
[year_published] => 1999
[isbn] => 11111111
[author_id] => 1,2,3
[author_name] => author 1,author 2,author 3
[publisher_id] => 1,2
[publisher_name] => publisher 1,publisher 2

Answer 1

You should use distinct in group_concat

  select books.*, 
    GROUP_CONCAT(distinct uthors.id) as author_id, 
    GROUP_CONCAT(distinct authors.name) as author_name,
    GROUP_CONCAT(distinct publishers.id) as publisher_id, 
    GROUP_CONCAT(distinct publishers.name) as publisher_name

    from books
    join authors_on_books on authors_on_books.book_id = books.id
    join authors on authors_on_books.author_id = authors.id
    join books_on_publishers on books_on_publishers.book_id = books.id
    join publishers on books_on_publishers.book_id = publishers.id
    group by books.id

But remember that group_concat(distinct...) have a certain impact on performance..

and as suggested by gordon linoff you could use left join for ensure that get all books

  select books.*, 
    GROUP_CONCAT(distinct uthors.id) as author_id, 
    GROUP_CONCAT(distinct authors.name) as author_name,
    GROUP_CONCAT(distinct publishers.id) as publisher_id, 
    GROUP_CONCAT(distinct publishers.name) as publisher_name

    from books
    left join authors_on_books on authors_on_books.book_id = books.id
    left join authors on authors_on_books.author_id = authors.id
    left join books_on_publishers on books_on_publishers.book_id = books.id
    left join publishers on books_on_publishers.book_id = publishers.id
    group by books.id