How to display data from multiple tables with a compound Join query?

Question

MySQL

Good evening!
There is the following request:

SELECT
prod.name,
prod.price,
cat.name,
atr.nameAttribute,
val.attributeValue
FROM products prod
INNER JOIN categories cat ON prod.idCategory = cat.idCategory
INNER JOIN productAttribute atr ON cat.idCategory = atr.idCategory
INNER JOIN attributeValue val ON cat.idCategory = val.idCategory

After execution, the Cartesian product of the strings is displayed. Those. lines are duplicated. How to display strings without duplication?

Answer 1

FROM products prod
It is clear, the main table.

INNER JOIN categories cat ON prod.idCategory = cat.idCategory

This is also understandable, the name of the category is attached to the product.

INNER JOIN productAttribute atr ON cat.idCategory = atr.idCategory

And here it is clear, we attach a list of attributes for this category.

INNER JOIN attributeValue val ON cat.idCategory = val.idCategory

But here it is not clear why for a product to take all the values ​​of the attributes of its category? You need to attach only the value of an already attached attribute for this product. idCategory in the attributeValue table should not exist at all.