CODEWITHSUNDEEP
phpmysqlpdo
Heidel
Heidel

Reputation: 3254

SQL many-to-many select query

There are 5 tables

books
------
id
name
year

author
------
id
name

store
------
id
name

books_authors
------
id
book_id
author_id

books_stores
------
id
store_id
book_id

Every book can have many authors and every book can be in a few stores. I would like to get book with all its authors and with all stores where it can be found

My code (I use PDO)

if(isset($_POST['books'])){
    $books_ids = $_POST["books"];
}

$books_ids_in = implode(',', array_fill(0, count($books_ids), '?'));

$query = "SELECT b.id, b.name, b.year, a.name as author_name, s.name as store_name
          FROM books as b
          LEFT JOIN books_authors as b_a
            ON b.id = b_a.book_id
          LEFT JOIN authors as a
            ON a.id = b_a.author_id
          LEFT JOIN books_stores as b_s
            ON b.id = b_a.book_id
          LEFT JOIN stores as s
            ON s.id = b_s.store_id
          WHERE b.id IN (". $books_ids_in .")
          ORDER BY b.id";

$stmt = $conn->prepare($query); 
foreach ($books_ids as $k => $id) {
    $stmt->bindValue(($k+1), $id);
}

$stmt->execute();
$results = $stmt->fetchAll();

but in this case I get two string if book has two author (for example) and a lot of string for every store. How can I get one result for every book with all its authors and stores?

Upvotes: 2

Views: 74

Answers (2)

O. Jones
O. Jones

Reputation: 108651

JOIN operations have the purpose of generating combinatorial explosions. Your extra rows are the result of such explosions.

You want one row per book, showing authors and stores. Each of these rows contains three kinds of information: book, authors, stores. So, you need subqueries that can generate exactly one result per book for each type of information. Then you need to join those subqueries together.

It's easy for books: you already have a table with one row per.

For authors you need this:

         SELECT ba.book_id, GROUP_CONCAT(a.name) author_names
           FROM books_authors ba
           LEFT JOIN authors a ON ba.author_id = a.id
          GROUP BY ba.book_id

If you know the authors for a book should be listed in order of id, use GROUP_CONCAT(a.author_name ORDER BY a.id). The order of authors is considered very important by bibliographers and the authors themselves, by the way.

That result set contains rows like

  17   James Watson, Francis Crick
  19   Dewey, Cheatham, Howe

You do the same sort of thing for your stores.

         SELECT bs.book_id, GROUP_CONCAT(s.name) store_names
           FROM books_stores bs
           LEFT JOIN stores s a ON bs.store_id = s.id
          GROUP BY bs.book_id

Then you JOIN your books table to these two subqueries (a/k/a virtual tables) and you have what you want.

SELECT b.id, b.name, aa.author_names, ss.store_names
  FROM books b
  LEFT JOIN (
             SELECT ba.book_id, GROUP_CONCAT(a.name) author_names
               FROM books_authors ba
               LEFT JOIN authors a ON ba.author_id = a.id
              GROUP BY ba.book_id
            )  aa ON b.id = aa.book_id
  LEFT JOIN (
             SELECT bs.book_id, GROUP_CONCAT(s.name) store_names
               FROM books_stores bs
               LEFT JOIN stores s a ON bs.store_id = s.id
              GROUP BY bs.book_id
            )  ss ON b.id = ss.book_id

That will give you

  17   The Double Helix   James Watson, Francis Crick   Barnes, Amazon, Harvard Coop
  19   Faking Contracts   Dewey, Cheatham, Howe         Law Bookstore, Amazon

Upvotes: 1

mitkosoft
mitkosoft

Reputation: 5316

You can use GROUP_CONCAT() function:

SELECT
    b.id,
    b.`name`,
    b.`year`,
    GROUP_CONCAT(DISTINCT a.`name`) AS author_names,
    GROUP_CONCAT(DISTINCT s.`name`) AS store_names
FROM
    books AS b
    LEFT JOIN books_authors AS b_a ON b.id = b_a.book_id
    LEFT JOIN authors AS a ON a.id = b_a.author_id
    LEFT JOIN books_stores AS b_s ON b.id = b_a.book_id
    LEFT JOIN stores AS s ON s.id = b_s.store_id
WHERE
    b.id IN (". $books_ids_in .")
GROUP BY b.id
ORDER BY b.id

Upvotes: 2

Related Questions