Explain how multiple left joins work?

Question

MySQL

There are several tables.

Books with id and name fields.
Genres with id and name fields.
Ratings with userid rating and bookid fields Book
genres with bookid and genreid fields.

The resulting query should give such a table.

name | rating | urating | genre name |
some | 8 | 5 | g1, g2, gn|

But the request:

SELECT 
b.name, 
AVG(br.rating) as rating, 
MAX(ubr.rating) as urating, 
GROUP_CONCAT(g.name) as genrename
FROM books b
LEFT JOIN book_rating br ON br.book_id = b.id
LEFT JOIN book_rating ubr ON ubr.book_id = b.id AND ubr.user_id = 1
LEFT JOIN book_genre bg ON bg.book_id = b.id
LEFT JOIN genre g ON g.id = bg.genre_id
WHERE b.id = 1
GROUP BY b.id

Outputs:

name | rating | urating | genre name |
some | 8 | 5 | g1, g2, gn, g1, g2, gn.|

That is, depending on the query variables, the result of the genrename column is repeated multiple times.
Somehow appending the rating table affects the result of the genrename column.

I understand that the query can be written differently, but this result was unexpected for me and I want to understand why it works like this?

PS I apologize for the crooked text. (I'm writing from my phone).

Answer 1

Good!
There can be several ratings for one book, genres, as I understand it, too, here the lines are doubled, to see this, just remove the grouping and run the request. This will help you understand what's going on. Better yet, remove the grouping and join the tables one by one, analyzing the result, everything will become clear.
If you explain on your fingers, then all the ratings of this book will be attached to each book id

book | rating
---------------
 1   |    4
 1   |    5

If you join another table, then the picture will be something like this

book | rating | gename
----------------------
  1   |  4    |  g1
  1   |  4    |  g2
  1   |  5    |  g1
  1   |  5    |  g2

Accordingly, after grouping you have:

book | rating | gename
----------------------
  1   |  4.5    |  g1, g2, g1, g2

And to solve the problem, use GROUP_CONCAT_DISTINCT
It will exclude duplicate values

book | rating | gename
----------------------
  1   |  4.5    |  g1, g2

ps In any incomprehensible situation, remove the grouping