Magento Question posted in
The official documentation can be found here.

Preference in Mysql resultset – Magento

While executing the below query in Mysql I am getting multiple records.

SELECT * 
  FROM catalog_product_entity_int 
 WHERE attribute_id = 99 
   and row_id = 378050

+----------+--------------+----------+--------+-------+
| value_id | attribute_id | store_id | row_id | value |
+----------+--------------+----------+--------+-------+
| 12101931 |           99 |        0 | 378050 |     4 |
| 21858725 |           99 |        3 | 378050 |     1 |
| 21861516 |           99 |        4 | 378050 |     1 |
+----------+--------------+----------+--------+-------+

I need a query which will fetch value for store_id 3 and if store_id 3 is not present then the value of store_id 0.

Tags:

2

Answers


  1. 0

    Try this please

    SELECT 
IF((SELECT Count(*) FROm catalog_product_entity_int WHERE store_id = 3) > 0,value ,
IF((SELECT Count(*) FROm catalog_product_entity_int  WHERE store_id = 0) > 0,value ,0)) value
  FROM catalog_product_entity_int 
 WHERE attribute_id = 99 
   and row_id = 378050
 GROUP BY row_id

    This would pick the value of store_id 3 first, if you have such a row in your selected rows.
    Then it tries to get the value of store_id 0 and if that also fails the value is 0.

    That last part you have to check, how you will detect that there is neither a store_id 3 or a store_id 0 in your selection.

    The group by clause should be controlled. Eventually attribute_id could be better

    Login or Signup to reply.
  2. 0

    Get the max store_id and join to source 

    drop table if exists t;
create table t
( value_id int, attribute_id int, store_id int, row_id int, value int);
insert into t values
( 12101931 ,           99 ,        0 , 378050 ,     4 ),
( 21858725 ,           99 ,        3 , 378050 ,     1 ),
( 21861516 ,           99 ,        4 , 378050 ,     1 ),
( 12101931 ,           99 ,        0 , 378051 ,     4 ),
( 21858725 ,           99 ,        5 , 378051 ,     1 ),
( 21861516 ,           99 ,        4 , 378051 ,     1 ),
( 12101931 ,           99 ,        1 , 378052 ,     4 ),
( 21858725 ,           99 ,        5 , 378052 ,     1 ),
( 21861516 ,           99 ,        4 , 378052 ,     1 ),
( 21861516 ,           99 ,        4 , 378053 ,     1 );


select t.* 
from t
join (select attribute_id, row_id, max(store_id) maxstore
        from t 
        where attribute_id = 99 and store_id in(0,3)
        group by  attribute_id, row_id) s 
        on s.attribute_id = t.attribute_id and s.row_id = t.row_id and s.maxstore = t.store_id;

+----------+--------------+----------+--------+-------+
| value_id | attribute_id | store_id | row_id | value |
+----------+--------------+----------+--------+-------+
| 21858725 |           99 |        3 | 378050 |     1 |
| 12101931 |           99 |        0 | 378051 |     4 |
+----------+--------------+----------+--------+-------+
2 rows in set (0.00 sec)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search