SQL Query for gettin all products with custom options

Question

Need help making a query which returns the sku, name, custom option name and Custom option values for all products with custom options.

Example:

sku | name | Custom option name | Custom option values

1 | Product 1 | Width | M, L

            Height | Red, Blue

2 | Product 2 | Width | M, L

            Height | Red, Yellow

Answer 1

With Price

SELECT e.sku
      ,t.title as 'name' 
      ,cv.value as 'Custom option name'
      ,GROUP_CONCAT(ot.title) as 'Custom option values'
      ,GROUP_CONCAT(price.price) as 'price'
FROM catalog_product_option o 
JOIN catalog_product_entity e ON e.entity_id=o.product_id 
JOIN catalog_product_option_title t ON t.option_id=o.option_id 

JOIN catalog_product_entity_varchar cv ON 
                   cv.entity_id=e.entity_id AND 
                   cv.attribute_id=(SELECT attribute_id 
                                    FROM eav_attribute ea 
                                    JOIN eav_entity_type et 
                                    ON  
                                         et.entity_type_code='catalog_product' 
                                         AND et.entity_type_id=ea.entity_type_id 
                                    WHERE ea.attribute_code='name')
LEFT JOIN catalog_product_option_type_value ov ON
           ov.option_id=o.option_id 
LEFT JOIN catalog_product_option_type_title ot ON 
           ot.option_type_id=ov.option_type_id 
left join catalog_product_option_type_price price on price.option_type_id = ot.option_type_id
GROUP BY e.sku, ov.option_id;

Answer 2

SELECT e.sku
      ,t.title as 'name' 
      ,cv.value as 'Custom option name'
      ,GROUP_CONCAT(ot.title) as 'Custom option values'
FROM catalog_product_option o 
JOIN catalog_product_entity e ON e.entity_id=o.product_id 
JOIN catalog_product_option_title t ON t.option_id=o.option_id 
JOIN catalog_product_entity_varchar cv ON 
                   cv.entity_id=e.entity_id AND 
                   cv.attribute_id=(SELECT attribute_id 
                                    FROM eav_attribute ea 
                                    JOIN eav_entity_type et 
                                    ON  
                                         et.entity_type_code='catalog_product' 
                                         AND et.entity_type_id=ea.entity_type_id 
                                    WHERE ea.attribute_code='name')
LEFT JOIN catalog_product_option_type_value ov ON
           ov.option_id=o.option_id 
LEFT JOIN catalog_product_option_type_title ot ON 
           ot.option_type_id=ov.option_type_id 
GROUP BY e.sku, ov.option_id;

tested on magento 1.9.2.3 MySQL 5.6.21

don't forget to add table prefix if you have one - check app/etc/local.xml (table_prefix tag)

sample output

+---------+-----------------------+----------------------------------------+----------------------+
| sku     | name                  | Custom option name                              | Custom option values |
+---------+-----------------------+------------------------------------  ----+----------------------+
| abl000  | Monogramming          | Isla Crossbody Handbag                      | NULL                 |
| mem000  | Membership start date | Madison Island VIP Membership - 1 Year | NULL                 |
| mtk004c | monogram              | Chelsea Tee                            | NULL                 |
| mtk004c | Test Custom Options   | Chelsea Tee                             | model 2,model 1      |
+---------+-----------------------+----------------------------------------+----------------------+