Getting super attributes info using raw SQL in Magento

Introduction

While creating a configurable product, you need to create an attribute(s) which will be used as a select option(s) for the customer(For Example Color, Size, etc.). These kinda attributes are called super attributes or configurable attributes.

In this article, we will be discussing how to get different info about super attributes using raw SQL.

1. Getting Super Attribute Info

SELECT 
  attribute_id,
  attribute_code,
  frontend_label 
FROM
  eav_attribute 
WHERE attribute_id IN 
  (SELECT 
    attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

2. Getting Super Attribute Labels

SELECT 
  value 
FROM
  catalog_product_super_attribute_label 
WHERE product_super_attribute_id IN 
  (SELECT 
    product_super_attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

3. Getting Super Attribute Prices

SELECT 
  is_percent, 
  pricing_value 
FROM
  catalog_product_super_attribute_pricing 
WHERE product_super_attribute_id IN 
  (SELECT 
    product_super_attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

4. Getting Super Attribute Products (Associated Simple Products)

SELECT 
  product_id 
FROM
  catalog_product_super_link 
WHERE parent_id = $productId

Note: $productId = product id of configurable product

5. Super Attributes Schema

Magento DB Schema Super Attributes
Magento DB Schema Super Attributes

Hope this article gave at least some info about Super Attributes.
Thanks for reading!

Cheers!!