In this table:
|value_id|entity_type_id|attribute_id|store_id|entity_id|value
|9289729 |4 |62 |0 |765985 |default value
|9289730 |4 |62 |1 |765985 |value in spanish
|9289731 |4 |62 |2 |765985 |value in dutch
I need to get the value for the store_id (that can be 1, 2 or 3) or the default value (store_id = 0) if it doesn't exist.
And it has to be included in a larger query composed of several INNER JOINS, which looks like this so far:
SELECT DISTINCT `p2c`.`product_id`, `name_id`.`value` AS `name`, `short_description_id`.`value` AS `short_description` FROM `catalog_category_product` AS `p2c`
INNER JOIN `catalog_product_entity_int` AS `status` ON p2c.product_id = status.entity_id AND status.value = 1
INNER JOIN `catalog_product_entity_int` AS `visibility` ON p2c.product_id = visibility.entity_id AND visibility.value = 4
INNER JOIN `catalog_product_entity_varchar` AS `name_id` ON p2c.product_id = name_id.entity_id AND name_id.attribute_id = 60
INNER JOIN `catalog_product_entity_text` AS `short_description_id` ON p2c.product_id = short_description_id.entity_id AND short_description_id.attribute_id = 62
WHERE (category_id IN ('1224'))
So what I want is something like:
SELECT DISTINCT `p2c`.`product_id`, `name_id`.`value` AS `name`, `short_description_id`.`value` AS `short_description` FROM `catalog_category_product` AS `p2c`
INNER JOIN `catalog_product_entity_int` AS `status` ON p2c.product_id = status.entity_id AND status.value = 1
INNER JOIN `catalog_product_entity_int` AS `visibility` ON p2c.product_id = visibility.entity_id AND visibility.value = 4
INNER JOIN `catalog_product_entity_varchar` AS `name_id` ON p2c.product_id = name_id.entity_id AND name_id.attribute_id = 60
INNER JOIN `catalog_product_entity_text` AS `short_description_id` ON p2c.product_id = short_description_id.entity_id
AND short_description_id.attribute_id = 62
//start of not real mysql code
AND ((short_description_id.store_id = 3) IF THERE IS A VALUE FOR THIS STORE_ID
OR (short_description_id.store_id = 0) OTHERWISE)
//end of not real mysql code
WHERE (category_id IN ('1224'))
Oh, and I'm using Zend's mysql, if that matters.
Any ideas?
One approach is to left join twice to catalog_product_entity_text. Once for ID 0 and another for ID 3 and then do a COALESCE in your select
SELECT
..
COALESCE(`short_description_id`.`value` , `short_description_id_DEFAULT`.`value`) AS `short_description`
..
FROM
...
LEFT JOIN `catalog_product_entity_text` AS `short_description_id`
ON p2c.product_id = short_description_id.entity_id
AND short_description_id.attribute_id = 62
AND (short_description_id.store_id = 3)
LEFT JOIN `catalog_product_entity_text` AS `short_description_id_DEFAULT`
ON p2c.product_id = short_description_id.entity_id
AND short_description_id.attribute_id = 62
AND (short_description_id.store_id = 0)