Jump to content

SQL Statement to Extract Stock Available by Product


Recommended Posts



I would like to run a SQL statement to obtain the product with the available stock. I try to run this but does not work:

SELECT prod.reference Referencia, prodat.reference RefAtrib, prodlang.name Producto, atlang.name Atributo, prod.price Precio, stock.quantity Cantidad
pr_product prod
LEFT JOIN pr_stock_available stock ON (prod.id_product = stock.id_product)
LEFT JOIN pr_product_attribute prodat ON (prod.id_product = prodat.id_product)
LEFT JOIN pr_product_lang prodlang ON (prod.id_product = prodlang.id_product),
LEFT JOIN pr_product_attribute_combination prodatcomb ON (prodat.id_product_attribute = prodatcomb.id_product_attribute),
LEFT JOIN pr_attribute_lang atlang ON (prodlang.id_lang = atlang.id_lang),
LEFT JOIN pr_attribute_lang atlang ON (prodatcomb.id_attribute = atlang.id_attribute)
prodlang.id_lang = 7

I also have this statement that WORKS but don't show the products without attributes (that are not on the attributes tables, in other words, without combination):

SELECT distinct pp.reference RefProd, pa.reference RefAtrib, pl.name Producto, p.quantity Cantidad
FROM pr_stock_available p
LEFT JOIN pr_product pp ON (p.id_product = pp.id_product)
LEFT JOIN pr_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN pr_product_attribute_combination pac ON (p.id_product_attribute = pac.id_product_attribute)
LEFT JOIN pr_product_attribute pa ON (p.id_product_attribute = pa.id_product_attribute)
LEFT JOIN pr_attribute_lang al ON (pac.id_attribute = al.id_attribute)
WHERE pl.id_lang = 7 AND al.id_lang = 7
ORDER BY RefAtrib, RefProd, Producto

Any idea would be helpful.


I am using prestashop

Link to comment
Share on other sites

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...