MySQL: Joining table duplicates SUM()
<p>I am working with a bilingual website that deals with products.</p>

<p>Each product has a <code>sku</code> and <code>supplier_id</code>. When inserting products, if the <code>sku</code> <strong>and</strong> <code>supplier_id</code> already exists, instead of creating a new product, I simply update its quantities.</p>

<p>If only the <code>sku</code> matches, but the <code>supplier_id</code> is different, then I create a new product.</p>

<p>Because the website is multi language, there are some tables for translations. </p>

<p>When I search products I display one for each <code>sku</code>, summing the quantities.</p>

<p>This is my query</p>

<pre><code>SELECT
    products.id, products.sku, products.slug,
    SUM(products.quantity) quantity,
    suppliers.name supplier_name,
    locale.description,
    categories.name category_name
FROM products products
    LEFT JOIN products_locale locale ON (products.id = locale.product_id AND locale.language_code = 'it')
    LEFT JOIN app_suppliers suppliers ON products.supplier_id = suppliers.id
    LEFT JOIN app_categories_locale categories ON (products.category_id = categories.category_id AND categories.language_code = 'it')
WHERE products.slug = 'slug'
GROUP BY products.sku
</code></pre>

<p>There are 2 different products with the same <code>sku</code>, one has quantity 5 and the other has 2.</p>

<p>The <code>quantity</code> field should return 7, instead it returns 9. Basically, the second product is duplicated.</p>

<p>Removing the <code>LEFT JOIN products_locale locale ON (products.id = locale.product_id AND locale.language_code = 'it')</code> solves the problem, but I need that field.</p>

<p>What is more strange is that I join two translation tables, <code>products_locale</code> and <code>app_categories_locale</code>, but only <code>products_locale</code> causes this behavior.</p>

<p>Is there any way of retrieving the same fields without duplicating the rows ?</p>

<p>Thank you</p>