I just don't know how to get all simple product's stock and SKU information with SQL Query. I just need to get something like this

SKU       Quantity   Stock Status
---       --------   ------------
454645       50           1
545878       15           1

Any help please.


2 Answers 2


Use following query :

"SELECT cpe.sku, csi.qty FROM catalog_product_entity cpe RIGHT JOIN cataloginventory_stock_item csi ON cpe.entity_id = csi.item_id GROUP BY cpe.sku ORDER BY cpe.sku ASC";

You can check in stock or out of stock according to the returned quantity

  • Thanks for the answer. I don't know why two column I have which is product_id and item_id. Both are same. Commented Feb 14, 2018 at 11:10
  • glad i can help Commented Feb 14, 2018 at 11:14
  • product_id instead of item_id worked for me Commented May 29, 2020 at 15:35

Here is my result. Maybe someone will need this. This is for v2.1.5. And to get only simple products.

select catalog_product_entity.sku, cataloginventory_stock_item.qty, cataloginventory_stock_status.stock_status from catalog_product_entity
    join cataloginventory_stock_item on catalog_product_entity.entity_id = cataloginventory_stock_item.product_id
    join cataloginventory_stock_status on catalog_product_entity.entity_id = cataloginventory_stock_status.product_id
    where type_id = "simple";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.