Export from the database brand, quantity and size/color varchar: MySQL & EE

Question

I'm looking to put together an amazon inventory report to help our product buyers with identifying products that we would like to send for FBA.

What I want to display:

Brand, Product Name, Size, Color, MAP Price, Sales Price, SKU, Qty.

What I want to sort by:

type_id, manufacturer_value, qty, type_id

Where I'm getting them:

• Brand, Name SKU and MAP Price are coming from catalog_product_flat_1

• Qty is coming from cataloginventory_stock_item

• Sales Price is coming from catalog_product_index_price

My problem happens when I try and bring in size and color. These attributes are stored as int in catalog_product_entity_int The varchar of the value is stored in eav_attribute_option_value So I have to join a table just to match the values, and bring the data from another additional table.

No big deal there. The problem is that I'm unable to bring both in at the same time. I think I need a subquery, but I'm not quite sure where I would use that, or how it would be acomplished. Here's the query that gets one value to show:

SELECT flat.manufacturer_value AS Brand, EAOV_size.Value AS Size, flat.type_id, flat.name, flat.price, price.final_price, flat.sku, cpev.value AS star_upc, stock.qty
FROM magento.catalog_product_flat_1 AS flat
LEFT JOIN magento.cataloginventory_stock_item AS stock ON ( stock.item_id = flat.entity_id )
LEFT JOIN magento.catalog_product_index_price AS price ON ( price.entity_id = flat.entity_id )
LEFT JOIN catalog_product_entity_varchar AS cpev ON ( cpev.entity_id = flat.entity_id ) AND (cpev.attribute_id = '291')
LEFT JOIN magento.catalog_product_entity_int AS CPEI ON (flat.entity_id = CPEI.entity_id)
INNER JOIN magento.eav_attribute_option_value AS EAOV_size ON ( EAOV_size.option_id = CPEI.Value ) AND ( CPEI.attribute_id = '141' )
WHERE flat.manufacturer_value = 'Montbell'
AND stock.qty > '0'
AND flat.type_id = 'simple'
GROUP BY flat.sku;

The last join is an inner, so it will display, if it's a LEFT it'll only show null. and for some reason full and full outer joins only return errors.

Any help is appreciated.

Answer 1

Figured it out, and got values from EAV instead of the flat_table:

    SELECT
    at_name.value AS name,
    at_manufacturer_value.value AS brand,
    at_price.value AS msrp,
    at_size_value.value AS size,
    at_color_value.value AS color,
    at_special_price.value AS sale_price,
    at_retailstarupc.value AS star_upc,
    e.sku,
    si.qty

    FROM magento.cataloginventory_stock_item si
    LEFT JOIN magento.catalog_product_entity e ON e.entity_id = si.product_id

    LEFT JOIN magento.catalog_product_entity_varchar at_name ON e.entity_id =         at_name.entity_id AND at_name.attribute_id = '60'
    LEFT JOIN magento.catalog_product_entity_decimal at_price ON e.entity_id = at_price.entity_id AND at_price.attribute_id = '64'
    LEFT JOIN magento.catalog_product_entity_decimal at_special_price ON e.entity_id = at_special_price.entity_id AND at_special_price.attribute_id = '65'
    LEFT JOIN magento.catalog_product_entity_varchar at_retailstarupc ON e.entity_id = at_retailstarupc.entity_id AND at_retailstarupc.attribute_id = '290'
    LEFT JOIN magento.catalog_product_entity_int at_manufacturer ON e.entity_id = at_manufacturer.entity_id AND at_manufacturer.attribute_id = '70'
    LEFT JOIN magento.eav_attribute_option_value at_manufacturer_value ON at_manufacturer.value = at_manufacturer_value.option_id AND at_manufacturer_value.store_id = '0'
    LEFT JOIN magento.catalog_product_entity_int at_size ON e.entity_id = at_size.entity_id AND at_size.attribute_id = '141'
    LEFT JOIN magento.eav_attribute_option_value at_size_value ON at_size.value = at_size_value.option_id AND at_size_value.store_id = '0'
    LEFT JOIN magento.catalog_product_entity_int at_color ON e.entity_id = at_color.entity_id AND at_color.attribute_id = '80'
    LEFT JOIN magento.eav_attribute_option_value at_color_value ON at_color.value = at_color_value.option_id AND at_color_value.store_id = '0'

    WHERE e.type_id = 'simple'
    HAVING qty > 0 AND brand = "Arc'teryx";