如何使用链接表正确显示记录

Question

I'm having trouble displaying database records in the manner I want using PHP and Mysql.

I have 3 tables, BOOKS, AUTHORS, BOOKS_AUTHORS.

The BOOKS table has the following columns, book_id, title, publish_date, thumbnail. The AUTHORS table has the following columns, author_id, first_name, last_name. The BOOKS_AUTHORS table has the following columns, books_authors_id, book_id (linked to book_id in the BOOKS table), and author_id (linked to author_id in the AUTHORS table).

Here's the SQL I'm using to display the results.

SELECT books.book_id, title, publish_date, thumbnail, authors.author_id, first_name, last_name, books_authors.book_id, books_authors.author_id
FROM books, authors, books_authors
WHERE books.book_id = books_authors.book_id 
AND authors.author_id = books_authors.author_id 
AND books.book_id = (INSERT NUMBER HERE)

Everything works fine unless a book has more than 1 author. Let's say a book has 2 authors. I will get back 2 records that have the same title, publish_date, and thumbnail but each will have a different authors' name. So now I have 2 duplicate titles, 2 duplicate publish_dates, and 2 duplicate titles. This isn't how I want to display the records. I want both author names to appear under 1 title, 1 publish_date, and 1 thumbnail.

Answer 1

Try grouping by the fields you don't want duplicated, then using GROUP_CONCAT on the author information to have each author listed in the same row:

SELECT books.book_id, title, publish_date, thumbnail,
GROUP_CONCAT(CONCAT(authors.author_id,' ', first_name,' ', last_name))
FROM books, authors, books_authors
WHERE books.book_id = books_authors.book_id 
AND authors.author_id = books_authors.author_id 
AND books.book_id = (INSERT NUMBER HERE)
GROUP BY books.book_id, title, publish_date, thumbnail

I would also use an inner join instead of listing your tables and then a where clause. This is typically easier to read. So the middle section would be:

FROM books
INNER JOIN books_authors ON books.book_id = books_authors.book_id 
INNER JOIN authors ON authors.author_id = books_authors.author_id 
WHERE books.book_id = (INSERT NUMBER HERE)