How to get the product name by the sku in an inner join

Question

I am trying to do a join from inventory_source_item (sku column) to get a list of product names.

i currently have

SELECT * FROM `inventory_source_item` order by source_item_id desc limit 61

What table can i join to where i can join the sku to get a list of 61 product names? Cant find it anywhere!

Answer 1

Try this:

SELECT 
    inventory_source_item.*,
    catalog_product_entity.entity_id AS product_id,
    catalog_product_entity_varchar.value AS product_name
FROM 
    inventory_source_item 
INNER JOIN 
    catalog_product_entity ON 
        inventory_source_item.sku = catalog_product_entity.sku
INNER JOIN 
    catalog_product_entity_varchar ON 
        catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id AND
        catalog_product_entity_varchar.store_id = 0 AND
        catalog_product_entity_varchar.attribute_id = (
            SELECT 
                attribute_id 
            FROM 
                eav_attribute 
            WHERE
                attribute_code = 'name' AND
                entity_type_id = (
                    SELECT
                        entity_type_id
                    FROM
                        eav_entity_type
                    WHERE
                        entity_type_code = 'catalog_product'
                )
        )

Note: this is a expensive join, so avoid using it on a page that is being loaded often as it will be using alot of the databases resources.