MYSQL - Set data into one column rather then two

Question

Here is a mysql query that results two rows As you can see the second row is coming from this sub query in below query

LEFT JOIN catalog_product_entity_decimal AS cped ON cped.row_id = simple.entity_id
    AND `attribute_id` IN (SELECT `attribute_id` FROM `eav_attribute`
    WHERE `attribute_code` IN ('price','special_price')) AND cped.store_id IN (13,0)

The row has price = price & other has special_price = price, i want them into single row is that possible ?

SELECT parent.entity_id AS parent_id,
    simple.entity_id AS simple_id,
    simple.sku AS simple_sku,
    cpp.rule_price AS rule_price,
    cped.value AS price,
    cpev.value AS simple_name,
    parent.type_id AS type_id,
    wi.store_id AS wishlist_store_id
FROM catalog_product_entity AS parent
JOIN catalog_product_super_link AS link ON parent.row_id = link.parent_id
JOIN catalog_product_entity AS simple ON link.product_id = simple.entity_id
LEFT JOIN catalogrule_product_price AS cpp ON cpp.product_id = simple.entity_id AND
    cpp.website_id = (SELECT `website_id` FROM `store` WHERE `store_id` = '13')
LEFT JOIN catalog_product_entity_decimal AS cped ON cped.row_id = simple.entity_id
    AND `attribute_id` IN (
        SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('price','special_price')) 
    AND cped.store_id IN (13,0)
LEFT JOIN catalog_product_entity_varchar AS cpev ON cpev.row_id = simple.entity_id
    AND cpev.store_id IN (13,0) AND cpev.attribute_id = (
        SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('name') 
        order by `attribute_id` DESC 
        LIMIT 1)
LEFT JOIN wishlist_item AS wi ON parent.row_id = wi.product_id AND `wishlist_id` = '1'
WHERE parent.entity_id = '9244' 
order by cped.store_id DESC 
LIMIT 2

I have tried LIMIT 1 at the end of the query but then it does not return 'special price' in row — , Jul 20 '21 at 08:34

Barmar · Accepted Answer · 2021-07-20T09:56:28.077

0

This is a pivot, so use the usual idiom for pivoting in MySQL: aggregate a conditional with each value.

SELECT parent.entity_id AS parent_id,
    simple.entity_id AS simple_id,
    simple.sku AS simple_sku,
    cpp.rule_price AS rule_price,
    MAX(IF(cped.attribute_code = 'price', cped.value, NULL)) AS price,
    MAX(IF(cped.attribute_code = 'special_price', cped.value, NULL)) AS special_price,
    cpev.value AS simple_name,
    parent.type_id AS type_id,
    wi.store_id AS wishlist_store_id
FROM catalog_product_entity AS parent
JOIN catalog_product_super_link AS link ON parent.row_id = link.parent_id
JOIN catalog_product_entity AS simple ON link.product_id = simple.entity_id
LEFT JOIN catalogrule_product_price AS cpp ON cpp.product_id = simple.entity_id AND
    cpp.website_id = (SELECT `website_id` FROM `store` WHERE `store_id` = '13')
LEFT JOIN (SELECT cped.store_id, cped.row_id, cped.value, ea.attribute_code
           FROM catalog_product_entity_decimal AS cped
           JOIN eav_attribute AS ea ON ea.attribute_id = cped.attribute_id
           WHERE ea.attribute_code IN ('price','special_price')
           AND cped.store_id IN (13,0)) AS cped ON cped.row_id = simple.entity_id
LEFT JOIN catalog_product_entity_varchar AS cpev ON cpev.row_id = simple.entity_id
    AND cpev.store_id IN (13,0) AND cpev.attribute_id = (
        SELECT `attribute_id` FROM `eav_attribute`
        WHERE `attribute_code` IN ('name') 
        order by `attribute_id` DESC 
        LIMIT 1)
LEFT JOIN wishlist_item AS wi ON parent.row_id = wi.product_id AND `wishlist_id` = '1'
WHERE parent.entity_id = '9244' 
GROUP BY parent_id, simple_id, simple_sku, rule_price, simple_name, type_id, wishlist_store_id
ORDER BY cped.store_id DESC
LIMIT 1

edited Jul 20 '21 at 09:56

answered Jul 20 '21 at 08:51

Barmar

741,623
53
500
612

Error in query (1055): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'local_dollskill.simple.entity_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Jul 20 '21 at 09:33
Very thanks for the answer but it shows this error on executing – Jul 20 '21 at 09:34
1

See https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – Barmar Jul 20 '21 at 09:38
Yes checking on it – Jul 20 '21 at 09:38
not getting exactly, not sure how it helps. – Jul 20 '21 at 09:46
I've updated the answer. You have to add all the other columns to the `GROUP BY` list. – Barmar Jul 20 '21 at 09:48
That works but returns 4 rows, i am adding LIMIT 1 as i need only one valid record. is won't cause any issue right sir ? – Jul 20 '21 at 09:49
Why don't you just select the store ID that you want in the `WHERE` clause? – Barmar Jul 20 '21 at 09:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/235093/discussion-between-newtophp-and-barmar). – Jul 20 '21 at 09:51
https://stackoverflow.com/questions/68527699/update-sql-query-add-order-by – Jul 26 '21 at 12:47

MYSQL - Set data into one column rather then two

1 Answers1