I am trying to join these 2 tables together to get all the attributes I need from the group of Product Entity tables and join them with the remaining quantity in stock from the Stock Item table. However, the queries I am running right now are not providing accurate information. I am thinking that getting the product_id to join with the catalogiventory_stock_item table is not right.

Does anyone have an idea how these 2 tables can be joined? I know there has to be an intermediate table to get product_id for the entity_id in the catalog_product_entity table.

The query below only returns 40 rows, which I know is not correct.


select cpe.entity_id, cpe.entity_type_id, cpe.attribute_set_id, cpe.type_id, 
cpe.sku, cpe.created_at, cpe.updated_at, cpe.has_options, 
from magento.catalog_product_link cpl
inner join 
magento.catalog_product_entity cpe
on cpl.link_id = cpe.entity_id
inner join
magento.cataloginventory_stock_item csi
on cpl.product_id = csi.product_id
where csi.qty > 0;

1 Answer 1


The product_id field in cataloginventory_stock_item is a FK that points to the entity_id field of the catalog_product_entity table.
I think the join is correct. There must be something else wrong with your query.
For further help regarding the db schema try this: http://www.magereverse.com/
It is not up to date, but you will find relevant information presented in an interactive way.

Your Answer

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

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