2

How to get products name and sku and url key using sql query in magento 2 ?

3 Answers 3

4

SQL QUERY (Magento 2.1 and earlier):

SELECT nametable.value, 
           nametable.store_id, 
           catalog_product_entity.sku 
FROM   `catalog_product_entity_varchar` AS nametable 
           LEFT JOIN catalog_product_entity 
                  ON nametable.entity_id = catalog_product_entity.entity_id 
WHERE  nametable.attribute_id = (SELECT attribute_id 
                                     FROM   `eav_attribute` 
                                     WHERE  `entity_type_id` = 4 
                                            AND `attribute_code` LIKE 'name') 

SQL QUERY (Magento 2.2 and later):

SELECT nametable.value, 
   nametable.store_id, 
   catalog_product_entity.sku 
FROM   `catalog_product_entity_varchar` AS nametable 
   LEFT JOIN catalog_product_entity 
          ON nametable.row_id = catalog_product_entity.row_id 
WHERE  nametable.attribute_id = (SELECT attribute_id 
                             FROM   `eav_attribute` 
                             WHERE  `entity_type_id` = 4 
                                    AND `attribute_code` LIKE 'name') 

As mentioned by Shashank Kumrawat, Sku value is stored in catalog_product_entity and name field value is stored in catalog_product_entity_varchar

As Magento support multi-store data, So for single SKU, multiple row can be exits.catalog_product_entity_varchar have store id field.If you >> want specific store name then just nametable.store_id = {StoreId}

1
  • perfect answer with explanation!!! made my day! +1 :) Nov 1, 2019 at 6:54
2

You can use directly sql queries in magento2 as shown on this link -

using object manager (not recommended)

https://webkul.com/blog/magento2-write-custom-mysql-query/

Without object manager (recommended)

How to call Direct SQL Queries and join to collection In Magento2

To get SKU, you need to use table catalog_product_entity

and to get product name, need to use table catalog_product_entity_varchar

1

For Magento 2.2x versions, use row_id instead of entity_id in above sql queries

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.