Magento SQL Query to show product manufacturer

Magento SQL Query to show product manufacturer

August 11, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento SQL Query to show product manufacturer

Solving Magento Product Manufacturer Query for SQL Join

As a seasoned developer specializing in e-commerce platforms, I often encounter queries regarding specific data relationships within Magento. In this post, we’ll address the common challenge of retrieving product manufacturers using SQL joins and resolve the discrepancy between your current query and the actual manufacturer listed in the admin panel.

Understanding the Magento Product Manufacturer Structure

Before diving into the SQL query, it’s crucial to understand how Magento 1.7.0.2 structures its product data. In this version, product attributes like manufacturers are stored in multiple tables:

  • eav_attribute: Stores attribute metadata.
  • catalog_category_entity_varchar: Contains category-specific attribute values (categories are associated with products).
  • catalog_product_entity: The main table for product information.

The manufacturer is not a direct attribute of the product entity but rather an associated entity accessed through categories. This design choice allows for multiple manufacturers per product, which can be complex when querying the data.

Analyzing Your Current Query

Let’s examine your provided SQL query:

SELECT
eav_attribute_option_value.value FROM
eav_attribute,
eav_attribute_option,
eav_attribute_option_value
WHERE
eav_attribute.attribute_code = 'manufacturer' AND
eav_attribute_option.attribute_id = eav_attribute.attribute_id AND
eav_attribute_option_value.option_id = eav_attribute_option.option_id

This query successfully retrieves the possible manufacturer options, but it doesn’t connect these options with any specific products or SKUs.

Retrieving Product Manufacturers Through SQL Join

To achieve your goal of joining product manufacturers with their respective SKUs, you’ll need to connect multiple tables. Here’s an updated version of the query that should provide the desired results:

SELECT
    cpe.sku,
    caev.value AS manufacturer
FROM
    catalog_product_entity AS cpe
JOIN
    catalog_category_entity_varchar AS caev
ON
    cpe.entity_id = caev.entity_id
WHERE
    caev.attribute_id IN (
        SELECT
            eav.attribute_id
        FROM
            eav_attribute AS eav
        WHERE
            eav.attribute_code = 'manufacturer'
    )

This query does the following:

  1. Starts with the catalog_product_entity table, which contains product information and SKUs.
  2. Joins this table with catalog_category_entity_varchar, which stores category-specific attribute values (in this case, manufacturer).
  3. Filters the results using a subquery that identifies the manufacturer’s attribute ID.

Conclusion: Joining Magento Data for Product Manufacturers

When working with Magento SQL queries, it’s essential to understand how data is structured across multiple tables. By leveraging appropriate join conditions and subqueries, you can successfully retrieve product manufacturers and connect them with their respective SKUs.

Remember that Magento 1.7.0.2 has a complex architecture designed for scalability and flexibility. If you encounter further challenges or need advanced customizations, consider exploring professional Magento development resources like those available at Laravel Company.

Happy coding!

Magento 1 Catalog Database Admin

Our network