SQL Query for custom attribute

Question

I have create custom attribute in product upc

I want custom query (sql) to get SKUs and UPCs

Can anyone tell me the related tables from where I can get data, or if anyone can share query that would be great.

Answer 1

Magento uses the Eav structure to save the product attribute value.

First, you have to find out your attribute type of upc.So, you have to open eav_attribute and run the below for getting backend_type]

SELECT attribute_id,backend_type,attribute_code FROM eav_attribute where entity_type_id =4 and attribute_code ='upc'

Suppose, this attribute backend_type is varchar then magento is save the value for products of this attribute at catalog_product_entity_varchar

Magento is saved product lie entity wise like

Date time type attribute  to catalog_product_entity_datetime
Decimal/Price type attribute to catalog_product_entity_decimal
Image type attribute to  catalog_product_entity_gallery
Dropdown type attribute  to catalog_product_entity_int
Image type catalog_product_entity_media_gallery
Image type catalog_product_entity_media_gallery_value
Image type catalog_product_entity_media_gallery_value_to_entity
text type attribute  catalog_product_entity_text
tier price catalog_product_entity_tier_price
Varchar type attribute  to  catalog_product_entity_varchar

So, you have to find the appropriate table of this attribute then run below Query like:

SELECT * FROM catalog_product_entity_varchar where attribute_id =(SELECT attribute_id FROM eav_attribute where entity_type_id =4 and attribute_code ='name')

Here I have done for the name attribute. For your case, you have to change catalog_product_entity_varchar with respect to your backend type of attribute.

If select specific attributes and their values below query helpful

select * from eav_attribute_option join eav_attribute_option_value on eav_attribute_option_value.option_id = eav_attribute_option.option_id where eav_attribute_option.attribute_id={attribute_id}