M2 - Images are different than sku.jpg

images-different-than-sku.sql

SELECT
  cpe.sku AS sku,
  image.value AS main_image,
  small_image.value AS main_image,
  thumbnail.value AS main_image,
  gal.value AS galimage
FROM
  catalog_product_entity cpe
  LEFT JOIN catalog_product_entity_varchar AS image
    ON cpe.row_id = image.row_id
    AND image.attribute_id = 87 #image
    AND image.store_id = 0
  LEFT JOIN catalog_product_entity_varchar AS small_image
    ON cpe.row_id = small_image.row_id
    AND small_image.attribute_id = 88
    AND small_image.store_id = 0
  LEFT JOIN catalog_product_entity_varchar AS thumbnail
    ON cpe.row_id = thumbnail.row_id
    AND thumbnail.attribute_id = 89
    AND thumbnail.store_id = 0
  LEFT JOIN catalog_product_entity_media_gallery_value AS galval
    ON (cpe.row_id = galval.row_id)
  LEFT JOIN catalog_product_entity_media_gallery AS gal
    ON (galval.value_id = gal.value_id)
WHERE image.value NOT LIKE CONCAT(
    '%',TRIM('-1' FROM SUBSTRING(cpe.sku, 4)),'%'
  ) # where image IS DIFFERENT than sku with or without last "-1" as many images don't have it

  AND gal.value LIKE CONCAT(
    '%',TRIM('-1' FROM SUBSTRING(cpe.sku, 4)),'%'
  ) # where gallery image like sku with or without last "-1" as many images don't have it

  AND gal.value NOT REGEXP 'a[0-9].jpg';#not a secondary image a1, a2 etc