Not getting all the products after executing my query (they appear in the application)

Question

Yesterday i tried to obtain all products from MAGENTO database. I did a query and i thought it was working BUT i was wrong.

I put a random SKU in the MAGENTO application, then i clicked on "search" and the product appeared but that SKU did not apprear in my query:

This is my query:

SELECT main.sku, code_brand.value AS code_brand,option_value_brand.value

FROM catalog_product_entity AS main 
LEFT JOIN catalog_product_entity_int AS code_brand ON main.entity_id = code_brand.entity_id
JOIN eav_attribute_option option_brand on option_brand.attribute_id = code_brand.attribute_id and option_brand.option_id = code_brand.value
JOIN eav_attribute_option_value option_value_brand on option_brand.option_id = option_value_brand.option_id

WHERE code_brand.attribute_id = (
    SELECT attribute_id 
    FROM eav_attribute 
    WHERE attribute_code LIKE 'code_brand'
    AND `entity_type_id` = (
        SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
);

Whats going on here?

EDIT: What about store_id = 0 and store_id = 1?

Can please help me to obtain ALL the products?

What should i modify?

This is the MAGENTO database diagram:

Answer 1

I'm still not sure what your requirements are, but I try to explain you what might be your problem: If you want't to get all the products - no matter if the attribute code_brand is set or not, you have to use join left consequently in your sql statement. Now I assume that either the SKU you have used to filter the result of your SQL client is from a product with no attribute selected for code_brand or your SQL client is still limiting the result set.

So here is the SQL statement to get all products and join the attribute value, if a value is set (otherwise you will get NULL in that column):

SELECT main.sku, code_brand.value AS code_brand,option_value_brand.value
FROM catalog_product_entity AS main 
LEFT JOIN eav_attribute attribute_brand on attribute_brand.attribute_code = 'code_brand' and attribute_brand.entity_type_id = 4
LEFT JOIN catalog_product_entity_int code_brand ON main.entity_id = code_brand.entity_id and code_brand.attribute_id = attribute_brand.attribute_id
LEFT JOIN eav_attribute_option option_brand on option_brand.attribute_id = code_brand.attribute_id and option_brand.option_id = code_brand.value
LEFT JOIN eav_attribute_option_value option_value_brand on option_brand.option_id = option_value_brand.option_id;

You can add where main.sku = '[what-ever-sku-you-want-to-filter]' to get the specific row for that sku.

I hope that helps.

Edit Assuming you want to join the varchar attribute code_business_unit to the above statement use this SQL (I've removed the columns for the option ids from the statement because you don't need them)

SELECT main.sku, option_value_brand.value AS code_brand, code_business_unit.value as code_business_unit
FROM catalog_product_entity AS main 
LEFT JOIN eav_attribute attribute_brand on attribute_brand.attribute_code = 'code_brand' and attribute_brand.entity_type_id = 4
LEFT JOIN eav_attribute attribute_business_unit on attribute_business_unit.attribute_code = 'code_business_unit' and attribute_brand.entity_type_id = 4
LEFT JOIN catalog_product_entity_int code_brand ON main.entity_id = code_brand.entity_id and code_brand.attribute_id = attribute_brand.attribute_id
LEFT JOIN eav_attribute_option option_brand on option_brand.attribute_id = code_brand.attribute_id and option_brand.option_id = code_brand.value
LEFT JOIN eav_attribute_option_value option_value_brand on option_brand.option_id = option_value_brand.option_id
LEFT JOIN catalog_product_entity_varchar code_business_unit ON main.entity_id = code_business_unit.entity_id and code_business_unit.attribute_id = attribute_business_unit.attribute_id;