SQL statement / php code to handle many authors for one book

Question

I'll try to make it simple. I have a table for my books, one for the authors and a junction table with the id from my books with the author ID.

Now my issue is, I don't want to list the books more than once in the case the book has many authors. I could use the Group By, but I'm looking for a way to list the book once and instead of showing only one author, in the case it has more than one, I'd like to add the other authors in the same listing.

Really thought about this during the day, and I can't think of a good way to do it. Any help is greatly appreciated!

Thanks!

Have a look at: http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql — favoretti, Oct 03 '12 at 01:52

score 4 · Answer 1 · answered Oct 03 '12 at 06:47

I think what you are looking for is GROUP_CONCAT

SELECT b.Name, GROUP_CONCAT(a.Name) AS Authors
FROM Book b 
    INNER JOIN BookAuthor ba
    ON b.ID = ba.BookID
    INNER JOIN Author a
    ON ba.AuthorID = a.ID
GROUP BY b.Name;

Provides the output

50 Shades of Grey                                           EL James
Design Patterns: Elements of Reusable Object-Oriented Software  Ralph
Johnson,Erich Gamma,John Vlissides,Richard Helm
Harry Potter and the Goblet of Fire                         JK Rowling 
Harry Potter and the  Philosopher`s Stone                   JK Rowling

SQL Fiddle here

score 1 · Answer 2 · answered Oct 03 '12 at 01:58

Do two SQL statements. Something like this...

$sql = "SELECT id, name FROM books";

foreach ($conn->query($sql) as $book) {
    $sql = "SELECT a.name
            FROM authors a, author_books ab
            WHERE ab.book_id = $book['id']
            AND ab.author_id = a.id";

    foreach ($conn->query($sql) as $author) {
        echo $row["name"];
    }
}

Cheers

score 0 · Answer 3 · answered Oct 03 '12 at 03:04

see this example

CREATE TABLE book
(
bID INT,
bName VARCHAR(100),
PRIMARY KEY (bID)
);
--
CREATE TABLE author
(
aID INT,
aName VARCHAR(100),
PRIMARY KEY (aID)
);

CREATE TABLE book_author
(
aID INT ,
bID INT ,
PRIMARY KEY (aID,bID),
FOREIGN KEY (aID) REFERENCES author(aID),
FOREIGN KEY (bID) REFERENCES book(bID)
); 

INSERT INTO author (aID, aName) VALUES
    (1, 'The Doctor'),
    (2, 'Dalek1'),
    (3, 'Dalek2'),
    (4, 'Amy'),
    (5, 'Rory');

INSERT INTO book (bID, bName) VALUES
    (1, 'The Book of Crazy'),
    (2, 'Is Time Really Time'),
    (3, 'I Think without Pants'),
    (4, 'Perfection'),
    (5, 'Rise of the Daleks ');

INSERT INTO book_author (aID, bID) VALUES
    (4, 1),
    (1, 2),
    (4, 2),
    (5, 2),
    (1, 3),
    (2, 4),
    (2, 5),
    (3, 5);

----------
--the query
SELECT DISTINCT b.bName
FROM book b,author a,book_author ba
WHERE ba.aID=a.aID and ba.bID=b.bID and (a.aName = 'Amy' or a.aName='The Doctor')

--------------
--output
--Is Time Really Time
--I Think without Pants
--The Book of Crazy

all you need to do is use the DISTINCT keyword and follow Justin Wood's method

SQL statement / php code to handle many authors for one book

3 Answers3