1

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

1 Answer 1

0

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

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.