Is it possible to have multiple composite (aka "many-to-many", ManyToMany) joins that don't make the result set huge

Situation: Table book is associated with one or more authors via the _author_book table. It is also associated with one or more genres via the _book_genre table.

When selecting all the books, and all their genres, and all their authors, the number of rows returned is (assume each book has at least one genre and author):

• PROBLEM: books.map( |book| book.genres.size * book.authors.size).sum

what I want is:

• DESIRED: books.map( |book| [book.genres.size, book.authors.size].max).sum

so given 10 books, each book has 5 genres and 4 authors:

• PROBLEM: 200 rows returned (5 * 4 * 10)
• DESIRED: 50 rows returned ( [5,4].max * 10 )

Example Data:

mysql> SELECT * FROM book

 id | title
----+---------------------------
 1  | Dune: The Butlerian Jihad
 2  | The Talisman

mysql> SELECT * FROM genre

 id | title
----+-------------
 1  | Military
 2  | Horror
 3  | Thriller
 4  | Sci-Fi
 5  | Fiction
 6  | Speculative

mysql> SELECT * FROM author

 id | title
----+------------------
 1  | Brian Herbert
 2  | Kevin J Anderson
 3  | Stephen King
 4  | Peter Straub

mysql> SELECT * FROM _author_book

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

mysql> SELECT * 开发者_C百科FROM _book_genre

 book_id | genre_id
---------+-----------
 1       | 1
 1       | 4
 1       | 5
 1       | 6
 2       | 2
 2       | 3
 2       | 5

Here's what's wrong:

mysql> SELECT book.id AS "book.id", ..., author.fullname AS "author.fullname" FROM book
    -> LEFT JOIN _book_genre ON book.id = _book_genre.book_id
    -> LEFT JOIN genre ON genre.id = _book_genre.genre_id
    -> LEFT JOIN _author_book ON book.id = _author_book.book_id
    -> LEFT JOIN author ON author.id = _author_book.author_id;

 book.id | book.title | genre.id | genre.name  | author.id | author.fullname
---------+------------+----------+-------------+-----------+------------------
 1       | Dune: The… | 1        | Military    | 1         | Brian Herbert
 1       | Dune: The… | 4        | Sci-Fi      | 1         | Brian Herbert
 1       | Dune: The… | 5        | Fiction     | 1         | Brian Herbert
 1       | Dune: The… | 6        | Speculative | 1         | Brian Herbert
---------+------------+----------+-------------+-----------+------------------
 1       | Dune: The… | 1        | Military    | 2         | Kevin J Anderson
 1       | Dune: The… | 4        | Sci-Fi      | 2         | Kevin J Anderson
 1       | Dune: The… | 5        | Fiction     | 2         | Kevin J Anderson
 1       | Dune: The… | 6        | Speculative | 2         | Kevin J Anderson
---------+------------+----------+-------------+-----------+------------------
 2       | The Talis… | 2        | Horror      | 3         | Stephen King
 2       | The Talis… | 3        | Thriller    | 3         | Stephen King
 2       | The Talis… | 5        | Fiction     | 3         | Stephen King
---------+------------+----------+-------------+-----------+------------------
 2       | The Talis… | 2        | Horror      | 4         | Peter Straub
 2       | The Talis… | 3        | Thriller    | 4         | Peter Straub
 2       | The Talis… | 5        | Fiction     | 4         | Peter Straub

Hopefully it is clear what the problem is. The result of the first join, on genre/_book_genre, is being joined with all the author results, resulting in a resultset of 14 rows, when really a result set of only seven rows would suffice:

 book.id | book.title | genre.id | genre.name  | author.id | author.fullname
---------+------------+----------+-------------+-----------+------------------
 1       | Dune: The… | 1        | Military    | 1         | Brian Herbert
 1       | Dune: The… | 4        | Sci-Fi      | 2         | Kevin J Anderson
 1       | Dune: The… | 5        | Fiction     | 1         | Brian Herbert
 1       | Dune: The… | 6        | Speculative | 2         | Kevin J Anderson
---------+------------+----------+-------------+-----------+------------------
 2       | The Talis… | 2        | Horror      | 3         | Stephen King
 2       | The Talis… | 3        | Thriller    | 4         | Peter Straub
 2       | The Talis… | 5        | Fiction     | 3         | Stephen King

(alternatively, the duplicated authors could be NULL values, but if there IS an answer out there, I am assuming it will result in the authors being repeated as above).

My Question: is there some combination of LEFT, RIGHT, INNER, OUTER, A-B-A-B SELECT START that will prevent the many-to-many results from multiplying the number of rows?

Group by and max is what you want.

Your second result set seems wrong:

1       | Dune: The… | 1        | Military    | 1         | Brian Herbert
1       | Dune: The… | 4        | Sci-Fi      | 2         | Kevin J Anderson

Can hardly mean what you want. Try wording the resultset how you want it, and you will probably be able to construct the statement you are looking for. Unless you can word it properly, people will have a hard time answering this question, or at least I will.