0

I have the following table

create table catalog_product_entity_varchar
(
    value_id     int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id     smallint unsigned default 0 not null comment 'Store ID',
    entity_id    int unsigned      default 0 not null comment 'Entity ID',
    value        varchar(255)                null comment 'Value',  
)

I want to find all entity_ids where attribute_id = 86 for the same store_id where the value is the same.

For example for the columns attribute_id, store_id, entity_id, value

86, 1, 1, mypath

is duplicate with

86, 1, 2, mypath

but is not duplicate with

86, 2, 1, mypath

The result would be entity_id, store_id, value

Any help is appreciated.

1

1 Answer 1

1

You can accomplish this with grouping on your store_id and value in a CTE or subquery to get the dupes and then re-joining the dupes to your catalog_product_entity_varchar table like so:

WITH CTE_Dupes AS
(
     SELECT store_id, value
     FROM catalog_product_entity_varchar
     WHERE attribute_id = 86
     GROUP BY store_id, value
     HAVING COUNT(1) > 1
)

SELECT C.entity_id, C.store_id, C.value
FROM catalog_product_entity_varchar C
INNER JOIN CTE_Dupes D
    ON C.store_id = D.store_id
    AND C.value = D.value
WHERE C.attribute_id = 86
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.