Most sold products in shop transactions

Question

I am trying to build a query to select the most sold products given the number of top products to fetch and the language code. I don't know to do it right so I would appreciate you help.

shop_transactions
id
1
2
3

shop_transaction_products_match
id | shop_transaction_id | product_id | units_bought
1    1                     1            4
2    2                     2            1
3    3                     2            2


products_translations
id | product_id | language_code | name | seo_name
1    1            es              Hola   hola
2    1            en              Hey u  hey-u
3    2            es              Adiós  adios
4    2            en              Bye u  bye-u

products
id | category_id
1    1
2    2

product_categories
id
1
2

product_categories_translations
id | category_id | language_code | name | seo_name
1    1             es              AA     aa
2    1             en              BB     bb
3    2             es              CC     cc
4    3             en              DD     dd

The information per row is: name from product_translations, seo_name from product_translations, name from product_categories_translations, seo_name from product_categories_translations

Rows ordered descendant by number products sold in total.

Considering the example information, the result if number of products to fetch is 2 and the language is es, would be:

Hola, hola, AA, aa -> 4 units sold of this product

Adiós, adios, CC, cc -> 3 units sold of this product

Thank you!

Edit:

The code I tried so far... and I am missing some things that I dont know how to do it:

SELECT pt.name, pt.seo_name, pct.name as name_category, pct.seo_name as seo_name_category
                                  From product_translations pt, products p, product_categories pc, product_categories_translations pct
                                  where p.id in (Select product_id from shop_transaction_product_match where shop_transaction_id in
                                  (Select id from shop_transactions)) AND pt.language_code = :language_code AND pct.language_code = :language_code
                                  AND p.category_id = pct.category_id

Answer 1

Try this one:

SELECT DISTINCT
 pt.name, pt.seo_name, pct.name, pct.seo_name,
 SUM(stpm.units_bought) units_sold
FROM products AS p
 LEFT JOIN product_categories AS pc ON p.category_id = pc.id
 LEFT JOIN products_translations AS pt ON p.id = pt.product_id AND pt.language_code = 'es'
 LEFT JOIN product_categories_translations AS pct ON pc.id = pct.category_id AND pct.language_code = 'es'
 LEFT JOIN shop_transaction_products_match AS stpm ON p.id = stpm.product_id
-- Where p.id = 1
GROUP BY p.id
ORDER BY units_sold DESC
limit 2

Answer 2

Instead of using IN it is better to use INNER JOIN.

SELECT products_translations.name,
           products_translations.seo_name,
           product_categories_translations.name,
           product_categories_translations.seo_name,
           SUM(shop_transaction_products_match.units_bought) AS Product_count
    FROM
         shop_transactions 
    INNER JOIN 
         shop_transaction_products_match
    ON shop_transactions.id=shop_transaction_products_match.shop_transaction_id
    INNER JOIN 
         products_translations 
    ON products_translations.product_id = shop_transaction_products_match.product_id
    INNER JOIN 
         products 
    ON products.id = shop_transaction_products_match.product_id
    INNER JOIN
        product_categories 
    ON products.category_id = product_categories.id
    INNER JOIN
        product_categories_translations
    ON product_categories_translations.category_id = product_categories.id AND product_categories_translations.language_code = products_translations.language_code 
    WHERE products_translations.language_code= 'es' GROUP BY products.id
    Limit 2

Hope this helps.