How Do You Compare 2 Records From The Same MySQL Table

Question

Is a way to compare 2 records from the same table? I already compared two tables to make sure my records are accurate using

SELECT * 
FROM `catalog_category_entity_varchar` c2t
WHERE NOT EXISTS (
    SELECT * 
    FROM `core_url_rewrite` c 
    WHERE c.category_id = c2t.entity_id
)

Now I am trying to compare the records in catalog_category_entity_varchar for any inconsistancies. Here's an example of my two records.

catalog_category_entity_varchar:

Record 1:
value_id:             68
entity_type_id:      3
attribute_id:        43
store_id:               0
entity_id:             10
value:                shop-by

Record 2:
value_id:             73
entity_type_id:      3
attribute_id:        57
store_id:               0
entity_id:             10
value:                shop-by.html

entity_id is the unique Identifier. I have to compare the value of url key which is attribute_id = 43 to value of url which is attribute_id 57. I assume I have to use the wildcard % after, which will remove the .html, and another before, which will remove part of the url on any level 2+ category urls (ex. catalog/shirts/shop-by.html).

If it makes it easier I can duplicate the table, and my initial compare statement, I just need to know how to modify the query to match the attribute ID's and use the wildcard.

I'm looking for mistakes and misspelling. For example, if shop-by.html is misspelled like shop-byy.html so I can correct all records that may be misspelled, or if the url is completely incorrect. This is a magento table, and magento will not reindex flat files and a few others if the url does not match url-key. — N2BH

Tom Tom · Accepted Answer · 2013-08-17T00:46:39

I'm not sure this will do everything you would like to do, but I think this may be a step in the right direction. It will compare the record with the 43 attribute with the 57 attribute and see if the value field of the 43 record is NOT in the value field of the 57 record.

SELECT  *

FROM    catalog_category_entity_varchar t1

        JOIN catalog_category_entity_varchar t2
        ON t1.entity_id = t2.entity_id
        AND t2.attribute_id = 57

WHERE   t1.attribute_id = 43
        AND INSTR(t2.value, t1.value) = 0;

How Do You Compare 2 Records From The Same MySQL Table

1 Answers