Magento 1 sql query for products with custom attributes

Question

Need sql query which get products attributes including custom attributes like following: product_id,product_type,sku,Product_Name,Brand,Color,Size,Price,Special_Price,inventory_type

Answer 1

Following is the query for my problem you can get hint from the following query and modify it as per your requirements(update attributed ids as per your Database):

SELECT 
 e.entity_id AS product_id,
 e.type_id AS product_type,
 e.sku AS sku,
 j.value AS Product_Name,
 mB.value AS Brand,
 mc.value AS Color,
 mcs.value AS Size,
 p1.value AS Price,
 ps1.value AS Special_Price,
 itV.value AS inventory_type,
 CASE
 WHEN s.value = '1' THEN 'Enabled'
 WHEN s.value = '2' THEN 'Disabled'
 ELSE NULL
 END AS product_status
FROM
 catalog_product_entity AS e
 LEFT JOIN
 catalog_product_entity_int AS it ON it.attribute_id = 241
 AND it.entity_type_id = '4'
 AND it.STORE_ID = 0
 AND e.entity_id = it.entity_id
 LEFT JOIN
 eav_attribute_option_value itV ON itV.option_id = it.value
 AND itV.STORE_ID = 0
 LEFT JOIN
 catalog_product_entity_text AS si ON si.attribute_id = 133
 AND si.entity_type_id = '4'
 AND si.STORE_ID = 0
 AND e.entity_id = si.entity_id
 LEFT JOIN
 catalog_product_entity_varchar AS j ON j.attribute_id = 71
 AND j.entity_type_id = '4'
 AND j.STORE_ID = 0
 AND e.entity_id = j.entity_id
 LEFT JOIN
 catalog_product_entity_varchar AS spsk ON spsk.attribute_id = 168
 AND spsk.entity_type_id = '4'
 AND spsk.STORE_ID = 0
 AND e.entity_id = spsk.entity_id
 LEFT JOIN
 catalog_product_entity_int AS s ON s.attribute_id = 96
 AND s.entity_type_id = '4'
 AND s.STORE_ID = 0
 AND e.entity_id = s.entity_id
 LEFT JOIN
 catalog_product_entity_int AS m ON m.attribute_id = 81
 AND m.entity_type_id = '4'
 AND m.STORE_ID = 0
 AND e.entity_id = m.entity_id
 LEFT JOIN
 eav_attribute_option_value mB ON mB.option_id = m.value
 AND mB.STORE_ID = 0
 LEFT JOIN
 catalog_product_entity_int AS clm ON clm.attribute_id = 92
 AND clm.entity_type_id = '4'
 AND clm.STORE_ID = 0
 AND e.entity_id = clm.entity_id
 LEFT JOIN
 eav_attribute_option_value mc ON mc.option_id = clm.value
 AND mc.STORE_ID = 0
 LEFT JOIN
 catalog_product_entity_int AS cls ON cls.attribute_id = 148
 AND cls.entity_type_id = '4'
 AND cls.STORE_ID = 0
 AND e.entity_id = cls.entity_id
 LEFT JOIN
 eav_attribute_option_value mcs ON mcs.option_id = clm.value
 AND mcs.STORE_ID = 0
 LEFT JOIN
 catalog_product_entity_decimal p1 ON p1.attribute_id = 75
 AND m.entity_type_id = '4'
 AND m.STORE_ID = 0
 AND e.entity_id = p1.entity_id
 LEFT JOIN
 catalog_product_entity_decimal ps1 ON ps1.attribute_id = 76
 AND m.entity_type_id = '4'
 AND m.STORE_ID = 0
 AND e.entity_id = ps1.entity_id
WHERE
 s.value = 1