Relationship between catalog_product_entity and cataloginventory_stock_item

Question

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.

Thanks.

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, 
cpe.required_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;

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.