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.